Re: Arrays, placeholders, and column types

Dan Sugalski <dan(at)sidhe(dot)org> writes:
> I've got some SQL statements that I'm issuing from my app using the
> PQexecParams() C call. All the parameters are passed in as literal
> string parameters (that is, the paramTypes array entry for each
> placeholder is set to 0) letting the engine convert.

> INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3, $4, $5])
> DB error is: ERROR: column "xyzzy" is of type numeric[] but
> expression is of type text[]

The ARRAY[] construct forces determination of the array type, and it
defaults to text[] in the absence of any type information from the array
components. (There's been some discussion of allowing the array type
determination to be postponed further, but we haven't thought of a good
way to do it yet.) What you'll need to do is specify at least one of
the array elements to be "numeric", either via paramTypes or with a cast
in the SQL command:

INSERT INTO foo (bar, baz, xyzzy) VALUES ($1, $2, ARRAY[$3::numeric, $4, $5])

regards, tom lane