Thread: Question about Oracle compatibility
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
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
theassociated data type. There is effectively only one null value and that value is a member of every SQL
datatype. There is no <literal> for a null value, although the keyword NULL is used in some places to indicate
thata 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 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
Serviceand other goodies at http://donb.photo.net.
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 Serviceand 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
Serviceand 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 differentqueries 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 truein 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 Serviceand other goodies at http://donb.photo.net.