Re: 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/