Fwd: int type problem in 7.3

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(at)sime(dot)com>
To: pgsql-hackers(at)postgresql(dot)org

It seems queries like:
select ... from table where id='' (an empty string) do not work anymore, it
worked up to 7.2. This will make migration to 7.3 quite difficult for some
application, especially for oracle applications. Would'nt it be better to
evaluate such expressions to false.

Regards,
Mario Weilguni

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

-------------------------------------------------------