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 <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > > 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 [EMAIL PROTECTED] > > ------------------------------------------------------- > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
