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