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@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

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

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

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.

No, oracle accepts this and works correctly with number() datatype.
However I did not know that in postgres '' was treated as '0'.

Regards,
Mario Weilguni

On Wed, 2 Oct 2002, Mario Weilguni wrote:

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.

No, oracle accepts this and works correctly with number() datatype.
However I did not know that in postgres '' was treated as '0'.

So what would I be selecting in Oracle if I did:

SELECT * FROM mytable WHERE myfield = ''

where myfield is of VARCHAR type?

If you want to select on NULL, whether or not you think the database is more
intelligent than you in determining what you really want, then write your query
to select on NULL. The chances are your database is not actually a mind reader.

--
Nigel J. Andrews

This document:
http://developer.postgresql.org/docs/postgres/release-7-2-3.html

mentions a release date of 2002-10-01 for version 7.2.3.

It isn't on the main website, tough, is it?

Regards,
Michael

On Wed, 2 Oct 2002, Michael Paesold wrote:

This document:
http://developer.postgresql.org/docs/postgres/release-7-2-3.html

mentions a release date of 2002-10-01 for version 7.2.3.

It isn't on the main website, tough, is it?

The documentation on the developers website is not necessarily
accurate - especially when it comes to dates. Documentation is
typically one of the last things finalized and is in a constant
state of change. That's one of the reasons why the developer
site is separated from the main website - people read things on
the developer site and think they're 100% accurate. Nothing is
final until it's announced on the announce mailing list and/or
the main website.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
http://www.camping-usa.com http://www.cloudninegifts.com
http://www.meanstreamradio.com http://www.unknown-artists.com
==========================================================================

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: