Re: Concatenation Operator: Is this a bug?

On Sat, Mar 17, 2001 at 02:31:30PM -0800,
Matt Friedman <matt(at)daart(dot)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