From: Lee Harr <missive@frontiernet.net>
Subject: Re: Concatenating does not work properly
Date: Thu, 10 Apr 2003 01:00:50 +0000 (UTC)
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 40
Message-ID: <b72fo2$27e0$1@news.hub.org>
References: <b719sp$24ki$1@news.hub.org>
Reply-To: missive@hotmail.com
User-Agent: slrn/0.9.7.3 (FreeBSD)
To: pgsql-sql@postgresql.org

In article <b719sp$24ki$1@news.hub.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;