NULLS and string concatenation

I have a function that uses an execute statement to insert data into a
table, I do in my implementation of table partitioning.

Anyway, I ran into trouble when NULL values were being passed in
(fields are nullable) and my insert statement turned into a big NULL.

Here's an equivalent statement that caused trouble:

select 'some text, should be null:'|| NULL

This returns NULL and no other text. Why is that? I wasn't expecting
the "some text.." to disappear altogether.

Is this a bug?

I was able to work around the problem by using COALESCE (and casting
variables since it wants the same data types passed to it).

-Don

--
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
312-560-1574

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: NULLS and string concatenation

On Fri, Nov 19, 2004 at 11:12:38 -0600,
Don Drake wrote:
> I have a function that uses an execute statement to insert data into a
> table, I do in my implementation of table partitioning.
>
> Anyway, I ran into trouble when NULL values were being passed in
> (fields are nullable) and my insert statement turned into a big NULL.
>
> Here's an equivalent statement that caused trouble:
>
> select 'some text, should be null:'|| NULL
>
> This returns NULL and no other text. Why is that? I wasn't expecting
> the "some text.." to disappear altogether.
>
> Is this a bug?

No.
>
> I was able to work around the problem by using COALESCE (and casting
> variables since it wants the same data types passed to it).

This is what you should do.

>
> -Don
>
> --
> Donald Drake
> President
> Drake Consulting
> http://www.drakeconsult.com/
> 312-560-1574
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: NULLS and string concatenation

Don Drake wrote:
> select 'some text, should be null:'|| NULL
>
> This returns NULL and no other text. Why is that? I wasn't expecting
> the "some text.." to disappear altogether.
>
> Is this a bug?

No. Null is "unknown" if you append unknown (null) to a piece of text,
the result is unknown (null) too.

If you're using NULL to mean something other than unknown, you probably
want to re-examine your reasons why.

> I was able to work around the problem by using COALESCE (and casting
> variables since it wants the same data types passed to it).

That's the correct procedure (although ask yourself if you should have
nulls rather than just empty strings).

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: NULLS and string concatenation

On Fri, Nov 19, 2004 at 11:45:43AM -0600, Bruno Wolff III wrote:
> On Fri, Nov 19, 2004 at 11:12:38 -0600, Don Drake wrote:
> >
> > I was able to work around the problem by using COALESCE (and casting
> > variables since it wants the same data types passed to it).
>
> This is what you should do.

If you don't mind using a non-standard feature, another possibility
would be to create an operator similar to || that COALESCEs NULLs
into empty strings.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: NULLS and string concatenation

On Fri, 19 Nov 2004 17:48:34 +0000, Richard Huxton wrote:
> Don Drake wrote:
> > select 'some text, should be null:'|| NULL
> >
> > This returns NULL and no other text. Why is that? I wasn't expecting
> > the "some text.." to disappear altogether.
> >
> > Is this a bug?
>
> No. Null is "unknown" if you append unknown (null) to a piece of text,
> the result is unknown (null) too.
>
> If you're using NULL to mean something other than unknown, you probably
> want to re-examine your reasons why.
>

I'm using NULL to mean no value. Logically, NULL is unknown, I agree.

I'm trying to dynamically create an INSERT statement in a function
that sometimes receives NULL values.

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.

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

-Don

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: NULLS and string concatenation

On Fri, 19 Nov 2004, Don Drake wrote:

> On Fri, 19 Nov 2004 17:48:34 +0000, Richard Huxton wrote:
> > Don Drake wrote:
> > > select 'some text, should be null:'|| NULL
> > >
> > > This returns NULL and no other text. Why is that? I wasn't expecting
> > > the "some text.." to disappear altogether.
> > >
> > > Is this a bug?
> >
> > No. Null is "unknown" if you append unknown (null) to a piece of text,
> > the result is unknown (null) too.
> >
> > If you're using NULL to mean something other than unknown, you probably
> > want to re-examine your reasons why.
> >
>
> I'm using NULL to mean no value. Logically, NULL is unknown, I agree.
>
> I'm trying to dynamically create an INSERT statement in a function
> that sometimes receives NULL values.
>
> 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 has some incompatibilities with the SQL spec (at least 92/99) wrt
NULLs and empty strings so it isn't a good comparison point. The spec is
pretty clear that if either argument to concatenation is NULL the output
is NULL.

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

I'm not sure it does actually. I'd have expected to see some general text
on how most operators return NULL for NULL input but a quick scan didn't
find any.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: NULLS and string concatenation

Don Drake 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 , General Rule 2a:

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

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: NULLS and string concatenation

Someone on this list provided me with a rather elegant solution to this a f=
ew weeks ago:

CREATE OR REPLACE FUNCTION text_concat_nulls_with_an_embedded_space(text, t=
ext) RETURNS text AS 'SELECT CASE WHEN $1 IS NULL THEN $2 WHEN $2 IS NULL T=
HEN $1 ELSE $1 || '' '' || $2 END' LANGUAGE sql;

CREATE OPERATOR ||~ (PROCEDURE =3D text_concat_nulls_with_an_embedded_space=
, LEFTARG =3D text, RIGHTARG =3D text);

And I call it as:
SELECT (trim(s_directio) ||~ trim(s_house) ||~ trim(s_post_dir) ||~ trim(s_=
street) ||~ trim(s_suffix)) as street ... (yadda yadda)

Deals quite neatly with the NULLs in some of the columns.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Fri 11/19/2004 9:53 AM
To: Don Drake; pgsql-sql@postgresql.org
Cc:=09
Subject: Re: [SQL] NULLS and string concatenation
On Fri, Nov 19, 2004 at 11:45:43AM -0600, Bruno Wolff III wrote:
> On Fri, Nov 19, 2004 at 11:12:38 -0600, Don Drake wr=
ote:
> >=20
> > I was able to work around the problem by using COALESCE (and casting
> > variables since it wants the same data types passed to it).
>=20
> This is what you should do.

If you don't mind using a non-standard feature, another possibility
would be to create an operator similar to || that COALESCEs NULLs
into empty strings.

--=20
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: NULLS and string concatenation

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Gregory S. Williamson wrote:
| Someone on this list provided me with a rather elegant solution to
this a few weeks ago:
|
| CREATE OR REPLACE FUNCTION
text_concat_nulls_with_an_embedded_space(text, text)
| RETURNS text
| AS 'SELECT CASE WHEN $1 IS NULL THEN $2 WHEN $2 IS NULL THEN $1 ELSE
$1 || '' '' || $2 END'
| LANGUAGE sql;

Ugly. As the previous poster mentioned, handling NULLs is what COALESCE
is for.

CREATE OR REPLACE
FUNCTION text_concat_nulls_with_an_embedded_space(text,text)
IMMUTABLE CALLED ON NULL INPUT RETURNS text
AS 'SELECT COALESCE($1 || '' '' || $2, $2, $1);'
LANGUAGE sql;

| CREATE OPERATOR ||~ (PROCEDURE =
text_concat_nulls_with_an_embedded_space, LEFTARG = text, RIGHTARG = text);
|
| And I call it as:
| SELECT (trim(s_directio) ||~ trim(s_house) ||~ trim(s_post_dir) ||~
trim(s_street) ||~ trim(s_suffix)) as street ... (yadda yadda)
|
| Deals quite neatly with the NULLs in some of the columns.

Or my personal favourite:

CREATE OR REPLACE FUNCTION comma_concat (text,text)
IMMUTABLE CALLED ON NULL INPUT RETURNS text
AS 'SELECT COALESCE ($1 || '','' || $2, $2);'
LANGUAGE sql;

CREATE AGGREGATE comma_concat (
~ BASETYPE=text,
~ SFUNC=comma_concat,
~ STYPE=text
);

Which is handy for 1:n reports like

SELECT grade, comma_concat($name) AS members
FROM test_results
GROUP BY grade;

- --
Andrew Hammond 416-673-4138 ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFBo4yHgfzn5SevSpoRAgjrAJ9M5WwQE1FOaxcs7o45KjdKZF6AQACg kCKS
V+qljFHFtYbOMcRU+7SawmY=
=xqTu
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)