Question about Oracle compatibility

On Mon, 27 Nov 2000, Mario Weilguni wrote:

Sorry if I'm posting to the wrong list, but I don't know which list is
appropriate for this question.

I've a question concerning compatibilty Postgres <-> Oracle. In Oracle,
empty strings and null are basicly the same, but it does not seem to
be under Postgres, making migration a pain.

Actually, they aren't the same at all under Oracle or under Postgres.

A null represents a lack of data, whereas an empty string is represents
data of zero length and zero content. Null is a state and not a value.

What you are probably seeing is a difference in table layout that sets
a default value of '' for the particular column you're touching. You can
have postgres do the same by specifying DEFAULT '' when you create your
table (or you could ALTER it in..).

Null values are actually quite important because they tell you when you
don't have data. An empty tring means something is there, whereas a null
in the same place means complete absense of all data.

Hope this helps.

Thanks

Alex

Example:
ORACLE:
select id
from anytable
where field='';

POSTGRES:
select id
from anytable
where field='' or field is null;

Or another example: The oracle query
update anytable set adatefiled=''
fails in Postgres, I've to write
update anytable set adatefield=null;

That seems really weird.

This gets really bad when the actual data is coming from a webinterface, I've
to handle 2 different queries for the case empty string and non-empty string.

Is there a better way to achieve this?

Thanks!

Best regards,
Mario Weilguni

--
Alex G. Perel -=- AP5081
veers@disturbed.net -=- alex.perel@inquent.com
play -=- work

Disturbed Networks - Powered exclusively by FreeBSD
== The Power to Serve -=- http://www.freebsd.org/

Mario Weilguni <mweilguni@sime.com> writes:

In Oracle, empty strings and null are basicly the same,

Are you sure about that? It'd be a pretty major failure to comply with
SQL standard semantics, if so.

SQL92 3.1 (Definitions):

null value (null): A special value, or mark, that is used to
indicate the absence of any data value.

SQL92 4.1 (Data types)

A null value is an implementation-dependent special value that
is distinct from all non-null values of the associated data type.
There is effectively only one null value and that value is a member
of every SQL data type. There is no <literal> for a null value,
although the keyword NULL is used in some places to indicate that a
null value is desired.

There is no room there for equating NULL with an empty string. I also
read the last-quoted sentence to specifically forbid treating the
literal '' as NULL.

regards, tom lane

At 06:09 PM 11/27/00 +0100, Mario Weilguni wrote:

Sorry if I'm posting to the wrong list, but I don't know which list is
appropriate for this question.

I've a question concerning compatibilty Postgres <-> Oracle. In Oracle,

empty

strings and null are basicly the same, but it does not seem to be under
Postgres, making migration a pain.

Go complain to Oracle - their behavior is NON-STANDARD. PG is doing it right.
An empty string isn't the same as NULL any more than 0 is the same as NULL for
the integer type. Adopting the Oracle-ism would break PG's SQL92-compliance
in this area.

This gets really bad when the actual data is coming from a webinterface,

I've

to handle 2 different queries for the case empty string and non-empty string.

Is there a better way to achieve this?

You could rewrite your logic to use the empty string rather than NULL, that's
one idea. In the OpenACS project, we ported nearly 10,000 lines of datamodel
plus a thousands of queries from Oracle to Postgres and wrote a little utility
routine that turned a string returned from a from into either NULL or 'the
string'
depending on its length. The select queries in the Oracle version were
properly
written using "IS NULL" so they worked fine. It sounds like you've got a
little
more work to do if the Oracle queries aren't written as "is null or ..."

This is a very nasty misfeature of Oracle, though, because porting from SQL92
to Oracle can be very difficult if the SQL92 compliant code depends on the
empty
string being different than NULL. Going to SQL92 from Oracle is easier and
you
can write the Oracle queries and inserts in an SQL92-compliant manner.

Benefits of doing so are that your stuff will be easier to port to InterBase,
etc as well as Postgres.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

At 12:39 PM 11/27/00 -0500, Alex Perel wrote:

On Mon, 27 Nov 2000, Mario Weilguni wrote:

Sorry if I'm posting to the wrong list, but I don't know which list is
appropriate for this question.

I've a question concerning compatibilty Postgres <-> Oracle. In Oracle,
empty strings and null are basicly the same, but it does not seem to
be under Postgres, making migration a pain.

Actually, they aren't the same at all under Oracle or under Postgres.

A null represents a lack of data, whereas an empty string is represents
data of zero length and zero content. Null is a state and not a value.

Unfortunately Mario's entirely correct (I use Oracle...)

insert into foo (some_string) values ('');

will insert a NULL, not an empty string, into the column some_string.

What you are probably seeing is a difference in table layout that sets
a default value of '' for the particular column you're touching. You can
have postgres do the same by specifying DEFAULT '' when you create your
table (or you could ALTER it in..).

Using "DEFAULT ''" might help some, but he specifically mentioned inserting
form data from a web page, and in this case he'll have to check the string
and explicitly insert NULL (or write a trigger for each table that does
the check and the resulting massage of the value) or rewrite his queries
to treat empty string as being the same as NULL explicitly.

Null values are actually quite important because they tell you when you
don't have data. An empty tring means something is there, whereas a null
in the same place means complete absense of all data.

Absolutely right, and Oracle's misimplementation truly sucks.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

At 12:44 PM 11/27/00 -0500, Tom Lane wrote:

Mario Weilguni <mweilguni@sime.com> writes:

In Oracle, empty strings and null are basicly the same,

Are you sure about that? It'd be a pretty major failure to comply with
SQL standard semantics, if so.

Thought you'd get a kick out of this:

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> create table fubar(some_string varchar(1000));

Table created.

SQL> insert into fubar values('');

1 row created.

SQL> select count(*) from fubar where some_string is null;

COUNT(*)
----------
1

SQL>

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

On Mon, 27 Nov 2000, Don Baccus wrote:

Actually, they aren't the same at all under Oracle or under Postgres.

A null represents a lack of data, whereas an empty string is represents
data of zero length and zero content. Null is a state and not a value.

Unfortunately Mario's entirely correct (I use Oracle...)

insert into foo (some_string) values ('');

will insert a NULL, not an empty string, into the column some_string.

I stand corrupted. I didn't remember this behavior. :/

What you are probably seeing is a difference in table layout that sets
a default value of '' for the particular column you're touching. You can
have postgres do the same by specifying DEFAULT '' when you create your
table (or you could ALTER it in..).

Using "DEFAULT ''" might help some, but he specifically mentioned inserting
form data from a web page, and in this case he'll have to check the string
and explicitly insert NULL (or write a trigger for each table that does
the check and the resulting massage of the value) or rewrite his queries
to treat empty string as being the same as NULL explicitly.

Might be easiest to feed the data through a simple stored proc. Doesn't take
long at all to whip something together for the purpose..

--
Alex G. Perel -=- AP5081
veers@disturbed.net -=- alex.perel@inquent.com
play -=- work

Disturbed Networks - Powered exclusively by FreeBSD
== The Power to Serve -=- http://www.freebsd.org/

Mario Weilguni writes:

This gets really bad when the actual data is coming from a
webinterface, I've to handle 2 different queries for the case empty
string and non-empty string.

In their documentation both Oracle 7 and 8 state:

Oracle currently treats a character value with a length of zero
as null. However, this may not continue to be true in future
releases, and Oracle recommends that you do not treat empty
strings the same as NULLs.

--
Pete Forman -./\.- Disclaimer: This post is originated
Western Geophysical -./\.- by myself and does not represent
pete.forman@westgeo.com -./\.- the opinion of Baker Hughes or
http://www.crosswinds.net/~petef -./\.- its divisions.

At 09:59 AM 11/28/00 +0000, Pete Forman wrote:

Mario Weilguni writes:

This gets really bad when the actual data is coming from a
webinterface, I've to handle 2 different queries for the case empty
string and non-empty string.

In their documentation both Oracle 7 and 8 state:

Oracle currently treats a character value with a length of zero
as null. However, this may not continue to be true in future
releases, and Oracle recommends that you do not treat empty
strings the same as NULLs.

Yeah, but this is harder than it sounds! NULL and '' are indistinguishable
in queries, so how do you treat them differently? Has to be in the
application code, I guess.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.