Skip site navigation (1) Skip section navigation (2)

Re: Question about Oracle compatibility

From: Alex Perel <veers(at)webhosting(dot)com>
To: Mario Weilguni <mweilguni(at)sime(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Question about Oracle compatibility
Date: 2000-11-27 17:39:32
Message-ID: Pine.BSF.4.10.10011271234340.10328-100000@rodent.webhosting.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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(at)disturbed(dot)net  -=-  alex(dot)perel(at)inquent(dot)com
               play  -=-  work                      
	 
Disturbed Networks - Powered exclusively by FreeBSD
== The Power to Serve -=- http://www.freebsd.org/     


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2000-11-27 17:44:46
Subject: Re: Question about Oracle compatibility
Previous:From: Marko KreenDate: 2000-11-27 17:16:51
Subject: Re: Re: [GENERAL] Warning: Don't delete those /tmp/.PGSQL.* files

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group