From: Tomas Vondra <tv@fuzzy.cz>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Differentiate Between Zero-Length String and NULLColumn Values
Date: Tue, 30 Jan 2007 20:34:13 +0100
Message-ID: <45BF9DB5.8020103@fuzzy.cz> (raw)
In-Reply-To: <20070130142332.GA31905@phlogiston.dyndns.org>
References: <20070130131334.GD31676@phlogiston.dyndns.org>
<45BF584F.A3DD.0030.0@indicator.be>
<20070130142332.GA31905@phlogiston.dyndns.org>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tv@fuzzy.cz, ajs@crankycanuck.ca
Subject: Re: Differentiate Between Zero-Length String and NULLColumn Values
In-Reply-To: <45BF9DB5.8020103@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox