I belive that I have found a bug - or maybe it was done on purpose. Have
a look at this query:
SELECT integer_field FROM bugtable WHERE (CASE WHEN '' <> '' THEN
integer_field = '' ELSE integer_field = 0 END);
Result: ERROR: invalid input syntax for integer: ""
SELECT text_field FROM bugtable WHERE (CASE WHEN '' <> '' THEN
text_field = '' ELSE text_field = '1' END);
Result: 1 rows fetched (0,02 sec)
Where table structure is:
CREATE TABLE "public"."bugtable" (
"integer_field" INTEGER,
"text_field" TEXT
) WITH OIDS;
I belive that PG reaction should be the same as in the second query --
there shouldn't be any errors.
$ psql --version
psql (PostgreSQL) 7.4
contains support for command-line editing
Windows XP/Cygwin
If this is not a bug, please tell me what is wrong.
ML