Обсуждение: Concatenation Operator: Is this a bug?

pgsql 7.0.3 linux

The following query behaves as I would expect if all the columns mentioned
have a value in them. If any of the columns are null however, the whole row
returns but it's blank.

I would expect for instance, if "title", "author", and "description" have
values but the others are null that I would get a row with just the text
from "title", "author", and "description" (concatenated) to be returned in
the row with the other values simply missing (since they are null). Instead
the whole row is returned but has no text, that is, it's blank.

SELECT
  title || ' ' || author || ' ' || description || ' ' || excerpt_title || '
' || excerpt_intro || ' ' || excerpt AS text
FROM
  books;

Is this an issue with null being considered a "non" value? Would a cast on
the columns help? Perhaps there is an ifnull return someting function?

Thanks,
Matt.

On Sat, Mar 17, 2001 at 02:31:30PM -0800,
  Matt Friedman <matt@daart.ca> wrote:
> pgsql 7.0.3 linux
>
> The following query behaves as I would expect if all the columns mentioned
> have a value in them. If any of the columns are null however, the whole row
> returns but it's blank.
>
> I would expect for instance, if "title", "author", and "description" have
> values but the others are null that I would get a row with just the text
> from "title", "author", and "description" (concatenated) to be returned in
> the row with the other values simply missing (since they are null). Instead
> the whole row is returned but has no text, that is, it's blank.

If if one of the operands in a concatenation operation is null, the result
is null. You can use coalesce to return an empty string if that's
what you want.


>   title || ' ' || author || ' ' || description || ' ' || excerpt_title || '
> ' || excerpt_intro || ' ' || excerpt AS text

For example:
   coalesce(title,'') || ' ' || coalesce(author,'') || ' ' || coalesce(description,'') || ' ' || excerpt_title || '
 ' || coalesce(excerpt_intro,'') || ' ' || coalesce(excerpt,'') AS text

Matt Friedman writes:

> The following query behaves as I would expect if all the columns mentioned
> have a value in them. If any of the columns are null however, the whole row
> returns but it's blank.

Correct.

> SELECT
>   title || ' ' || author || ' ' || description || ' ' || excerpt_title || '
> ' || excerpt_intro || ' ' || excerpt AS text
> FROM
>   books;
>
> Is this an issue with null being considered a "non" value?

Sort of.  More precisely, it's an issue with following the SQL standard.

> Would a cast on the columns help?

No.

> Perhaps there is an ifnull return someting function?

COALESCE(colname, 'value-if-null')

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/