concenation of strings and null values

[Tino Wildenhain]

Hi,

SELECT 'abc'::text || 'def'::text;

returns 'abcdef' as we know.

SELECT 'abc'::text || ''::text;

returns 'abc'

SELECT 'abc'::text || null::text;

returns null

The last example looks like a bug,
but if it is intentionally so, its
at least very annoying and inconvenient.

Can someone enlighten me if this is
in accordance to some not so transparent
rules of SQL92 or '99 and if so, how
to work around this?

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

[Nigel J. Andrews]

On Tue, 7 Oct 2003, Tino Wildenhain wrote:

Hi,

SELECT 'abc'::text || 'def'::text;

returns 'abcdef' as we know.

SELECT 'abc'::text || ''::text;

returns 'abc'

SELECT 'abc'::text || null::text;

returns null

The last example looks like a bug,
but if it is intentionally so, its
at least very annoying and inconvenient.
Looks correct to me. Sure, might be annoying but we'd rather have correctness I
think :)

What you're asking for the equivalent of is:

something1 || ???? || something3 to equate to: something1something3

which you obviously can't say in the general case because you don't know if
???? really is an empty string or in fact something much more significant as in
the example:

'do' || 'not' || 'concatinate'

Therefore the whole expression should evaluate to null otherwise you'd never
know you didn't actually know if you should or shouldn't concatinate.

Can someone enlighten me if this is
in accordance to some not so transparent
rules of SQL92 or '99 and if so, how
to work around this?

In this instance use coalesce() as in: SELECT 'abc' || coalesce(null,'');
--
Nigel J. Andrews
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Dieter Fischer]

Hello

SELECT 'abc'::text || null::text;

The last example looks like a bug,
but if it is intentionally so, its
at least very annoying and inconvenient.

NULL means here UNKOWN. You cannot add or concatenate something to an
undefined Value.

Regards

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

[Tino Wildenhain]

Nigel J. Andrews wrote:

On Tue, 7 Oct 2003, Tino Wildenhain wrote:

Hi,

SELECT 'abc'::text || 'def'::text;

returns 'abcdef' as we know.

SELECT 'abc'::text || ''::text;

returns 'abc'

SELECT 'abc'::text || null::text;

returns null

The last example looks like a bug,
but if it is intentionally so, its
at least very annoying and inconvenient.

Looks correct to me. Sure, might be annoying but we'd rather have correctness I
think :)

What you're asking for the equivalent of is:

something1 || ???? || something3 to equate to: something1something3

which you obviously can't say in the general case because you don't know if
???? really is an empty string or in fact something much more significant as in
the example:

not really, this behavoir is only with null values because all other
values are casted to text. So null (or none, or undefined ...) in most
languages map to "" (Empty string) when concenated.
It may be however the current behavior is according to SQL9[29],
since 1+null is null too (is a case in aggregates, isn't it?)
but I dont know of a human readable reference of the SQL specs.


'do' || 'not' || 'concatinate'

Therefore the whole expression should evaluate to null otherwise you'd never
know you didn't actually know if you should or shouldn't concatinate.

Can someone enlighten me if this is
in accordance to some not so transparent
rules of SQL92 or '99 and if so, how
to work around this?

In this instance use coalesce() as in: SELECT 'abc' || coalesce(null,'');

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

http://archives.postgresql.org

[Tom Lane]

Tino Wildenhain <ti**@wildenhain.de> writes:

Can someone enlighten me if this is
in accordance to some not so transparent
rules of SQL92 or '99 and if so, how
to work around this?

It is per spec: in SQL92 6.13 <string value expression>:

2) If <concatenation> is specified, then let S1 and S2 be the re-
sult of the <character value expression> and <character factor>,
respectively.

Case:

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 8: explain analyze is your friend

[Greg Stark]

Tino Wildenhain <ti**@wildenhain.de> writes:

So null (or none, or undefined ...) in most
languages map to "" (Empty string)

Not in SQL. In SQL null means "unknown". If it's unknown then it's still
unknown when there's something attached to the beginning of it.

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

[Gaetano Mendola]

Dieter Fischer (grid-it) wrote:

Hello

SELECT 'abc'::text || null::text;

The last example looks like a bug,
but if it is intentionally so, its
at least very annoying and inconvenient.

NULL means here UNKOWN. You cannot add or concatenate something to an
undefined Value.

You can but the result is UNKNOWN.

Regards
Gaetano Mendola