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(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.