Postgres Pro
Downloads
Home   >   mailing lists

Thread: concenation of strings and null values

concenation of strings and null values

From
Tino Wildenhain
Date:
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

Re: concenation of strings and null values

From
"Nigel J. Andrews"
Date:
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

Re: concenation of strings and null values

From
"Dieter Fischer (grid-it)"
Date:
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

Re: concenation of strings and null values

From
Tino Wildenhain
Date:
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 :-)

Re: concenation of strings and null values

From
Tom Lane
Date:
Tino Wildenhain <tino@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

Re: concenation of strings and null values

From
Greg Stark
Date:
Tino Wildenhain <tino@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

Re: concenation of strings and null values

From
Gaetano Mendola
Date:
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