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, 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@postgresql.org

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


Wed, 2 Oct 2002 09:00:17 +0200
Mario Weilguni <mweilguni@sime.com>
Fwd: int type problem in 7.3
Mario Weilguni <mweilguni@sime.com> writes:
> 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.

No, that was not what it did: in reality, the '' was silently taken as
zero, and would match rows containing 0.  That seems a very error-prone
behavior (not to say a flat-out bug) to me.

> But oracle accepts this one:
> SQL> select * from re_eintraege where id='';
> no rows selected
> because oracle treats the empty string as NULL

Oracle does that for string data, but it doesn't do it for numerics
does it?  In any case, that behavior is surely non-compliant with
the SQL spec.

We were not compatible with Oracle on this behavior before, and I'm
not very inclined to become so now.

			regards, tom lane

Wed, 02 Oct 2002 09:13:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Fwd: int type problem in 7.3 
Have you looked at transform_null_equals in the postgresql.conf file to 
see if turning that on makes this work like oracle?

On Wed, 2 Oct 2002, Mario Weilguni wrote:

> 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, 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@postgresql.org
> 
> -------------------------------------------------------
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 


Wed, 2 Oct 2002 10:30:48 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Fwd: int type problem in 7.3