pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNULLS and string concatenation
12+ messages / 10 participants
[nested] [flat]
* NULLS and string concatenation
@ 2004-11-19 17:12 Don Drake <dondrake@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Don Drake @ 2004-11-19 17:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NULLS and string concatenation
@ 2004-11-19 17:45 Bruno Wolff III <bruno@wolff.to>
parent: Don Drake <dondrake@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Bruno Wolff III @ 2004-11-19 17:45 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: pgsql-sql
On Fri, Nov 19, 2004 at 11:12:38 -0600,
Don Drake <dondrake@gmail.com> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NULLS and string concatenation
@ 2004-11-19 17:48 Richard Huxton <dev@archonet.com>
parent: Don Drake <dondrake@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Richard Huxton @ 2004-11-19 17:48 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NULLS and string concatenation
@ 2004-11-19 17:53 Michael Fuhr <mike@fuhr.org>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 12+ messages in thread
From: Michael Fuhr @ 2004-11-19 17:53 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; pgsql-sql
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 <dondrake@gmail.com> 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/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NULLS and string concatenation
@ 2004-11-19 18:40 Don Drake <dondrake@gmail.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 12+ messages in thread
From: Don Drake @ 2004-11-19 18:40 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
On Fri, 19 Nov 2004 17:48:34 +0000, Richard Huxton <dev@archonet.com> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NULLS and string concatenation
@ 2004-11-19 19:04 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Don Drake <dondrake@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Stephan Szabo @ 2004-11-19 19:04 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
On Fri, 19 Nov 2004, Don Drake wrote:
> On Fri, 19 Nov 2004 17:48:34 +0000, Richard Huxton <dev@archonet.com> 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NULLS and string concatenation
@ 2004-11-19 20:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Don Drake <dondrake@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2004-11-19 20:01 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NULLS and string concatenation
@ 2004-11-19 20:03 Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 2 replies; 12+ messages in thread
From: Gregory S. Williamson @ 2004-11-19 20:03 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; Don Drake <dondrake@gmail.com>; pgsql-sql
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;
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.
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:
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 <dondrake@gmail.com> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NULLS and string concatenation
@ 2004-11-19 20:14 Terry Lee Tucker <terry@esc1.com>
parent: Gregory S. Williamson <gsw@globexplorer.com>
1 sibling, 0 replies; 12+ messages in thread
From: Terry Lee Tucker @ 2004-11-19 20:14 UTC (permalink / raw)
To: pgsql-sql
Slick ;o)
This goes in my tool kit...
On Friday 19 November 2004 03:03 pm, Gregory S. Williamson saith:
> 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;
>
> 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.
>
> 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:
> 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 <dondrake@gmail.com>
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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Quote: 87
"The federal government has taken too much tax money from the people,
too much authority from the states, and too much liberty with the
Constitution."
--Ronald Reagan
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NULLS and string concatenation
@ 2004-11-19 20:21 Don Drake <dondrake@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Don Drake @ 2004-11-19 20:21 UTC (permalink / raw)
To: pgsql-sql
On Fri, 19 Nov 2004 15:01:42 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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
>
Thanks for the responses. I now have a better appreciation for the
SQL standard and PostgreSQL.
-Don
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NULLS and string concatenation
@ 2004-11-20 00:16 terry@greatgulfhomes.com
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 12+ messages in thread
From: terry@greatgulfhomes.com @ 2004-11-20 00:16 UTC (permalink / raw)
To: 'Stephan Szabo' <sszabo@megazone.bigpanda.com>; 'Don Drake' <dondrake@gmail.com>; +Cc: 'Richard Huxton' <dev@archonet.com>; pgsql-sql
There is an easy solution anyway, use coalesce to ensure you are never returning a null result for
any components of the concat.
e.g.
select 'some text, blah:' || coalesce(NULL, '')
equates to 'some text, blah:' || ''
hence
'some text, blah:'
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Stephan Szabo
> Sent: Friday, November 19, 2004 2:04 PM
> To: Don Drake
> Cc: Richard Huxton; pgsql-sql@postgresql.org
> Subject: Re: [SQL] NULLS and string concatenation
>
>
>
> On Fri, 19 Nov 2004, Don Drake wrote:
>
> > On Fri, 19 Nov 2004 17:48:34 +0000, Richard Huxton
> <dev@archonet.com> 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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: NULLS and string concatenation
@ 2004-11-23 19:16 Andrew Hammond <ahammond@ca.afilias.info>
parent: Gregory S. Williamson <gsw@globexplorer.com>
1 sibling, 0 replies; 12+ messages in thread
From: Andrew Hammond @ 2004-11-23 19:16 UTC (permalink / raw)
To: Gregory S. Williamson <gsw@globexplorer.com>; +Cc: Michael Fuhr <mike@fuhr.org>; Don Drake <dondrake@gmail.com>; pgsql-sql
-----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)
iD8DBQFBo4yHgfzn5SevSpoRAgjrAJ9M5WwQE1FOaxcs7o45KjdKZF6AQACgkCKS
V+qljFHFtYbOMcRU+7SawmY=
=xqTu
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2004-11-23 19:16 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-19 17:12 NULLS and string concatenation Don Drake <dondrake@gmail.com>
2004-11-19 17:45 ` Bruno Wolff III <bruno@wolff.to>
2004-11-19 17:53 ` Michael Fuhr <mike@fuhr.org>
2004-11-19 17:48 ` Richard Huxton <dev@archonet.com>
2004-11-19 18:40 ` Don Drake <dondrake@gmail.com>
2004-11-19 19:04 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-20 00:16 ` terry@greatgulfhomes.com
2004-11-19 20:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-19 20:21 ` Don Drake <dondrake@gmail.com>
2004-11-19 20:03 Re: NULLS and string concatenation Gregory S. Williamson <gsw@globexplorer.com>
2004-11-19 20:14 ` Terry Lee Tucker <terry@esc1.com>
2004-11-23 19:16 ` Andrew Hammond <ahammond@ca.afilias.info>
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