I think I have come across a bug in postgres 7.3.x. I noticed that when I
upgraded to 7.3 my code broke on certain sql statements. They still don't
work on the lastest stable release of 7.3.
Here is an example to illustrate my problem:
-- create the table with:
CREATE TABLE bugtest ( a int2 );
-- then do the following inserts:
insert into bugtest (a) select 1 union select 1; -- this one succeeds
insert into bugtest (a) select 1 union select '1'; -- this one also succeeds
insert into bugtest (a) select '1' union select 1; -- this one also succeeds
insert into bugtest (a) select '1' union select '1'; -- this one fails
The all succeed except the last one. It fails with the following error:
ERROR: column "a" is of type smallint but expression is of type text
You will need to rewrite or cast the expression
It seems to me that they should all succeed. At least postgres seems to
have no problem converting '<intvalue>' to <intvalue> anywhere else that I
can find. At least the last 3 inserts shoudl either all fail or all succeed
(IMHO).
Is this a bug? Has anyone else reported it? Is there a procedure I need to
follow to report it? Has it been fixed in 7.4?
On a sidenote I am doing the weird select union thing as a way to insert
many records at once without having to execute multiple queries. I first
started doing it on SQLServer and it was much, much faster than doing
separate inserts. Is there a better way to do it in postgres? I have
looked at the copy from command but I can't find any examples of how to use
it in php or how to specify the columns / column order that you are going to
use with php. Also what characters need to be excaped if I do this
(obviously new lines and tabs). And nulls are represented by \N.
Rick Gigger