Message-ID: <45BF9DB5.8020103@fuzzy.cz>
Date: Tue, 30 Jan 2007 20:34:13 +0100
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Thunderbird 1.5.0.8 (X11/20061119)
MIME-Version: 1.0
To: Andrew Sullivan <ajs@crankycanuck.ca>
CC: pgsql-sql@postgresql.org
Subject: Re: Differentiate Between Zero-Length String and NULLColumn
 Values
References: <20070130131334.GD31676@phlogiston.dyndns.org>
	<45BF584F.A3DD.0030.0@indicator.be>
	<20070130142332.GA31905@phlogiston.dyndns.org>
In-Reply-To: <20070130142332.GA31905@phlogiston.dyndns.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

> On Tue, Jan 30, 2007 at 02:38:07PM +0100, Bart Degryse wrote:
> 
>> Andrew, I think you're wrong stating that Oracle would interpret
>> NULL and empty string as equal. The Oracle databases I use (8, 9
>> and 10) certainly make a distiction between both values. Maybe
>> earlier versions did so, that I don't know.
> 
> Hmm.  Well, I'm not an Oracle guy, so I don't really know.  All I
> know is that we occasionally get people coming from Oracle who are
> surprised by this difference.  What I've been _told_ is that '' and
> NULL are under some circumstances (maybe integers?) the same thing,
> whereas of course ' ' and NULL are not.  But since I'm not an Oracle
> user, people should feel free to ignore me :)

I've recently read some books on Oracle, so probably the best thing I
can do is to quote a paragraph on this from "Oracle PL/SQL programming"
from O'Reilly:

In Oracle SQL and PL/SQL, a null string is 'usually' indistiguishable
from a literal of zero characters, represented literally as ''. For
example the following expression will evaluate to TRUE both in SQL and
PL/SQL:

   '' IS NULL

Assigning a zero-length string to a VARCHAR2(n) variable in PL/SQL also
yields a NULL result:

   DECLARE
      str VARCHAR2(1) := '';
   BEGIN
      IF str IS NULL   -- will be TRUE

This behavior is consistent with Oracle's treatment of VARCHAR2 table
columns.

...

These examples illustrate Oracle's partial adherence to the 92 and 99
versions of the ANSI SQL standard, which mandates a difference between a
zero-length string and a NULL string. Oracle admits the difference, and
says they may fully adopt the standard in the future. They've been
issuing that warning for about 10 years, though, and it hasn't happened yet.

...

Note: This does not apply to the CHAR(n) columns - these are
      blank-padded.

Tomas