Ok, I checked this again. Up until 7.2, it was possible to compare an empty string to a number, and it worked::
e.g.: select * from mytable where int4id=''
worked fine, but delivered no result. This is exactly what Oracle did here,
a comparison like this does not work:
SQL> select * from re_eintraege where id='foobar';
select * from re_eintraege where id='foobar' *
ERROR at line 1:
ORA-01722: invalid number
But oracle accepts this one:
SQL> select * from re_eintraege where id='';
no rows selected
because oracle treats the empty string as NULL and effectivly checks:
select * from re_eintraege where id is null;
I think 7.3 is doing right here and I've to fix all queries (*sigh*), but oracle compatibilty is lost here.
The bad news for me is, rewriting the queries won't help here, because I'll use indexing when I rewrite my queries to:
select 1 from mytable where id::text=''
Regards,Mario Weilguni
---------- Weitergeleitete Nachricht ----------
Subject: [HACKERS] int type problem in 7.3
Date: Wed, 2 Oct 2002 08:31:45 +0200
From: Mario Weilguni <mweilguni@sime.com>
To: pgsql-hackers@postgresql.org
It seems queries like:
select ... from table where id='' (an empty string) do not work anymore, itworked up to 7.2. This will make migration
to7.3 quite difficult for someapplication, especially for oracle applications. Would'nt it be better toevaluate such
expressionsto false.
Regards,Mario Weilguni
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
-------------------------------------------------------