X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 4FC353A4C55
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 19 Nov 2004 20:01:53 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 36868-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 19 Nov 2004 20:01:50 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 8FD113A4C5F
	for <pgsql-sql@postgresql.org>; Fri, 19 Nov 2004 20:01:50 +0000 (GMT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iAJK1gcQ013632;
	Fri, 19 Nov 2004 15:01:43 -0500 (EST)
To: Don Drake <dondrake@gmail.com>
Cc: Richard Huxton <dev@archonet.com>, pgsql-sql@postgresql.org
Subject: Re: NULLS and string concatenation 
In-reply-to: <6c21003b041119104039c88faf@mail.gmail.com> 
References: <6c21003b0411190912630d4afe@mail.gmail.com>
	<419E31F2.4060104@archonet.com>
	<6c21003b041119104039c88faf@mail.gmail.com>
Comments: In-reply-to Don Drake <dondrake@gmail.com>
	message dated "Fri, 19 Nov 2004 12:40:02 -0600"
Date: Fri, 19 Nov 2004 15:01:42 -0500
Message-ID: <13631.1100894502@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200411/206
X-Sequence-Number: 19525

Don Drake <dondrake@gmail.com> writes:
> This is still strange to me.  In Oracle, the same query would not
> replace the *entire* string with a NULL, it treats the NULL as a no
> value.

Oracle is a bit, um, standards-challenged.  They fail to make a
distinction between an empty string and a NULL, but such a distinction
is both logically necessary and required by the SQL standard.

> I can't find in the documentation where string concatenation of any
> string and NULL is NULL.

SQL92 section 6.13 <string value expression>, General Rule 2a:

            a) If either S1 or S2 is the null value, then the result of the
              <concatenation> is the null value.

			regards, tom lane