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
>
