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
>