X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id EB06C475ECE
	for <pgsql-sql@postgresql.org>; Wed,  9 Apr 2003 11:17:40 -0400 (EDT)
Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86])
	by postgresql.org (Postfix) with ESMTP id 24D8A475EC9
	for <pgsql-sql@postgresql.org>; Wed,  9 Apr 2003 11:17:40 -0400 (EDT)
Received: from [206.19.64.3] (account jconway HELO joeconway.com)
	by joeconway.com (CommuniGate Pro SMTP 4.0.4)
	with ESMTP-TLS id 1710424; Wed, 09 Apr 2003 08:53:34 -0700
Message-ID: <3E9439AF.9090505@joeconway.com>
Date: Wed, 09 Apr 2003 08:18:07 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Michal Taborsky <M.Taborsky@sh.cvut.cz>
Cc: pgsql-sql@postgresql.org
Subject: Re: Concatenating not working properly
References: <3E94343A.1060108@sh.cvut.cz>
In-Reply-To: <3E94343A.1060108@sh.cvut.cz>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Spam-Status: No, hits=-38.8 required=5.0
	tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT,
	REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MOZILLA_UA
	autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/136
X-Sequence-Number: 12837

Michal Taborsky wrote:
> 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 ?
> 

Try this:
SELECT
  (surname || ', ' || firstname || ' ' || secondname) as fullname,
   title_pre, surname, firstname, secondname IS NULL FROM person;

You'll find that when secondname is null, so is fullname. Instead of 
your original query, do (untested):

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


HTH,

Joe