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.

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;

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


Mon, 27 Nov 2000 18:09:52 +0100
Mario Weilguni <mweilguni@sime.com>
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/     


Mon, 27 Nov 2000 12:39:32 -0500 (EST)
Alex Perel <veers@webhosting.com>
Re: Question about Oracle compatibility
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

Mon, 27 Nov 2000 12:44:46 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Question about Oracle compatibility 
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.

Mon, 27 Nov 2000 10:38:24 -0800
Don Baccus <dhogaza@pacifier.com>
Re: Question about Oracle compatibility
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.

Mon, 27 Nov 2000 10:42:01 -0800
Don Baccus <dhogaza@pacifier.com>
Re: Question about Oracle compatibility
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.

Mon, 27 Nov 2000 10:47:27 -0800
Don Baccus <dhogaza@pacifier.com>
Re: Question about Oracle compatibility 
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/     


Mon, 27 Nov 2000 14:00:33 -0500 (EST)
Alex Perel <veers@webhosting.com>
Re: Question about Oracle compatibility
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.

Tue, 28 Nov 2000 09:59:38 +0000 (GMT)
Pete Forman <pete.forman@westgeo.com>
Re: Question about Oracle compatibility
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.

Tue, 28 Nov 2000 06:44:21 -0800
Don Baccus <dhogaza@pacifier.com>
Re: Question about Oracle compatibility