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 (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Fri, Nov 19, 2004 at 11:12:38 -0600,
Don Drake <dondrake (AT) gmail (DOT) com> wrote:
No.
This is what you should do.

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

Don Drake wrote:
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.

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

On Fri, Nov 19, 2004 at 11:45:43AM -0600, Bruno Wolff III wrote:
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

On Fri, 19 Nov 2004 17:48:34 +0000, Richard Huxton <dev (AT) archonet (DOT) com> wrote:
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 (AT) postgresql (DOT) org)

On Fri, 19 Nov 2004, Don Drake wrote:

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'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

Don Drake <dondrake (AT) gmail (DOT) com> writes:
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.

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

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

http://archives.postgresql.org

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 (AT) fuhr (DOT) org]
Sent: Fri 11/19/2004 9:53 AM
To: Don Drake; pgsql-sql (AT) postgresql (DOT) org
Cc:
Subject: Re: [SQL] NULLS and string concatenation
On Fri, Nov 19, 2004 at 11:45:43AM -0600, Bruno Wolff III wrote:
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