Обсуждение: 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
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
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
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/
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
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.
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
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 NULLTHEN $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 ... (yaddayadda) 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
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 libertywith theConstitution." --Ronald Reagan Work: 1-336-372-6812Cell: 1-336-363-4719 email: terry@esc1.com
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
-----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-----
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 >