Thread: Arrays, placeholders, and column types

I'm running into some problems with arrays in my SQL which're giving me fits.

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. The statements
look something like:

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

where the xyzzy column is an array. (I can't change this, it's a
holdover from the ISAM database scheme we're moving off of) When I
issue the command, the error I get back is:

   DB error is: ERROR:  column "xyzzy" is of type numeric[] but
expression is of type text[]
   HINT:  You will need to rewrite or cast the expression.

I've tried scattering to_number calls in the SQL, but this makes the
SQL look really messy, I'd much rather have it all handled on the
back end for consistency, and it really feels like I'm doing
something wildly wrong here anyway.

So, what am I doing wrong? Why isn't the back end converting the
parameters for array fields the way it does non-array fields? Is
there something simple and straightforward I can do to make this work
that I'm just missing here?
--
                Dan

--------------------------------------it's like this-------------------
Dan Sugalski                          even samurai
dan@sidhe.org                         have teddy bears and even
                                       teddy bears get drunk

Dan Sugalski <dan@sidhe.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

At 2:37 PM -0400 10/25/04, Tom Lane wrote:
>Dan Sugalski <dan@sidhe.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])

Hrm. Okay, not a problem. (I was assuming the column type would be
used to type the array, though I can see reasons to not do so) Is
there any particular speed advantage to casting over setting
paramTypes, or vice versa?
--
                Dan

--------------------------------------it's like this-------------------
Dan Sugalski                          even samurai
dan@sidhe.org                         have teddy bears and even
                                       teddy bears get drunk

Dan Sugalski <dan@sidhe.org> writes:
> At 2:37 PM -0400 10/25/04, Tom Lane wrote:
>> 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])

> Hrm. Okay, not a problem. (I was assuming the column type would be
> used to type the array, though I can see reasons to not do so)

Ideally it should be, but we haven't yet figured a reasonably clean way
to do it.  The problem is that the type assignment is made bottom-up,
and only if it's still unknown when we get up to the INSERT level can we
use the INSERT column types to affect it.  As a comparison point, if
you tried

    INSERT ... VALUES($1 + $2)

you'd get a complaint about being unable to choose a plus operator,
even though you might think the system ought to infer that from the
datatype of the destination column.

> Is there any particular speed advantage to casting over setting
> paramTypes, or vice versa?

I doubt it would make any visible difference.  Do what seems easiest for
your client code.

            regards, tom lane