Re: Concatenating does not work properly

In article <b719sp$24ki$1(at)news(dot)hub(dot)org>, Michal Taborsky wrote:
> Hello,
>
> I am facing a problem I cannot really explain to myself. I have a table
> with personal data which has columns surname, firstname, secondname,
> title and I want to do a simple select like this:
>
> SELECT (surname || ', ' || firstname || ' ' || secondname) as fullname
> FROM person
>
> For some rows (from what I recognised it is with rows, which have the
> title column empty) it works, for some it returns empty string:
>
> akcent=# SELECT (surname || ', ' || firstname || ' ' || secondname) as
> fullname, title_pre, surname, firstname, secondname FROM person;
>
> fullname | title_pre | surname | firstname | secondname
> -----------------------+-----------+----------+-----------+------------
> | Ing. | Taborsky | Michal |
> | Ing. | Barta | David |
> Novak, Josef | | Novak | Josef |
> Saroch, Adrian Walter | | Saroch | Adrian | Walter
> (4 rows)
>
> Is that a bug or am I missing something ?
>

I bet that one of surname, firstname, or secondname is NULL
in those rows where fullname is blank.

You might try:

SELECT (coalesce(surname, '') || ', ' ||
coalesce(firstname, '') || ' ' ||
coalesce(secondname, '')) as fullname
FROM person;