Обсуждение: 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