Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id E79419FB273
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 30 Jan 2007 10:08:43 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 84447-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 30 Jan 2007 10:08:17 -0400 (AST)
X-Greylist: delayed 00:30:02.453186 by SQLgrey-1.7.4
Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3])
	by postgresql.org (Postfix) with ESMTP id 0CA9C9FB1DA
	for <pgsql-sql@postgresql.org>; Tue, 30 Jan 2007 10:08:21 -0400 (AST)
Received: from webaccess.indicator.be ([10.100.1.7]) by
	interscan.indicator.com with InterScan Message Security Suite;
	Tue, 30 Jan 2007 14:26:37 +0100
Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise;
	Tue, 30 Jan 2007 14:38:15 +0100
Message-Id: <45BF584F.A3DD.0030.0@indicator.be>
X-Mailer: Novell GroupWise Internet Agent 7.0.1 
Date: Tue, 30 Jan 2007 14:38:07 +0100
From: "Bart Degryse" <Bart.Degryse@indicator.be>
To: <pgsql-sql@postgresql.org>
Subject: Re: Differentiate Between Zero-Length String and NULLColumn  Values
References: <016d01c74430$043b1b90$9b0014ac@wbaus090><20070130131334.GD31676
	@phlogiston.dyndns.org>
In-Reply-To: <20070130131334.GD31676@phlogiston.dyndns.org>
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=__PartF7D3002F.0__="
X-imss-version: 2.045
X-imss-result: Passed
X-imss-approveListMatch: *@indicator.be
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.073 tagged_above=0 required=5 tests=AWL, BAYES_50,
	FORGED_RCVD_HELO, HTML_40_50, HTML_MESSAGE, UNPARSEABLE_RELAY
X-Spam-Level: 
X-Archive-Number: 200701/156
X-Sequence-Number: 27308

--=__PartF7D3002F.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

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.

>>> Andrew Sullivan <ajs@crankycanuck.ca> 2007-01-30 14:13 >>>
On Tue, Jan 30, 2007 at 04:32:22PM +1100, Phillip Smith wrote:
> SHAMPOO","EQUEST","401600","0.00","0.00","0.00","0.00","10.00",""
                                                                 ^^
> SELECT * FROM tmpstk WHERE ean =3D NULL;
                                         ^^^^

Along with what Michael Fuhr said in his post about equality and
NULL, Postgres doesn't treat the empty string and NULL as equivalent
(because they're not).  Only Oracle has that dodgy interpretation of
SQL, as far as I know.  If you want to use the empty string, you need=20

WHERE ean =3D ''

If you want instead ean to be NULL, use the traditional \N to signify
NULL on your way in, or define null some other way.

A

--=20
Andrew Sullivan  | ajs@crankycanuck.ca=20
Users never remark, "Wow, this software may be buggy and hard=20
to use, but at least there is a lot of code underneath."
--Damien Katz

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--=__PartF7D3002F.0__=
Content-Type: text/html; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-15=
">
<META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>Andrew, I think you're wrong stating that Oracle would interpret NULL =
and empty string as equal.</DIV>
<DIV>The Oracle databases I use (8, 9 and 10) certainly make a distiction =
between both values.</DIV>
<DIV>Maybe earlier versions did so, that I don't know.</DIV>
<DIV><BR>&gt;&gt;&gt; Andrew Sullivan &lt;ajs@crankycanuck.ca&gt; =
2007-01-30 14:13 &gt;&gt;&gt;<BR>On Tue, Jan 30, 2007 at 04:32:22PM +1100, =
Phillip Smith wrote:<BR>&gt; SHAMPOO","EQUEST","401600","0.00","0.00","0.00=
","0.00","10.00",""<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ^^<BR>&gt; SELECT * FROM tmpstk WHERE ean =
=3D NULL;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp; ^^^^<BR><BR>Along with what Michael Fuhr said =
in his post about equality and<BR>NULL, Postgres doesn't treat the empty =
string and NULL as equivalent<BR>(because they're not).&nbsp; Only Oracle =
has that dodgy interpretation of<BR>SQL, as far as I know.&nbsp; If you =
want to use the empty string, you need <BR><BR>WHERE ean =3D ''<BR><BR>If =
you want instead ean to be NULL, use the traditional \N to signify<BR>NULL =
on your way in, or define null some other way.<BR><BR>A<BR><BR>-- =
<BR>Andrew Sullivan&nbsp; | ajs@crankycanuck.ca<BR>Users never remark, =
"Wow, this software may be buggy and hard <BR>to use, but at least there =
is a lot of code underneath."<BR>--Damien Katz<BR><BR>---------------------=
------(end of broadcast)---------------------------<BR>TIP 6: explain =
analyze is your friend<BR></DIV></BODY></HTML>

--=__PartF7D3002F.0__=--