Content-Type: text/plain;
  charset="iso-8859-1"
From: James Orr <james@orrwhat.net>
To: David BOURIAUD <david.bouriaud@ac-rouen.fr>,
	pgsql-sql@postgresql.org
Subject: Re: Is this a feature ?
Date: Tue, 4 Jun 2002 11:14:36 -0400
User-Agent: KMail/1.4.1
References: <200206041018.24328.david.bouriaud@ac-rouen.fr>
In-Reply-To: <200206041018.24328.david.bouriaud@ac-rouen.fr>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200206041114.36984.james@orrwhat.net>

On Tuesday 04 June 2002 04:18, David BOURIAUD wrote:
> Hi the list !
> I've read the docs, and found that you could concatenate two or more
> strings in one select statement like this :
> select
> 	civ || name || forname as identity
> from
> 	person;
>
> This just works fine and returns rows with only one column named identity.
> So far, if one of the fields (civ, name or forname) is null, identity is
> null... That doesn't seems right, since it means that 1+2+0 = 0 !!!!
> Has anybody else seen the same strange behaviour ? And is there a way to
> work this around ?
> Please, don't answer me to use the function textcat (text, text), since it
> behaves the same and makes queries quite unreadeable...
> eg.
> select
> 	textcat ( textcat (civ, name), forname) as identity
> from person;
> is quite readeable, but when you add fields separator, it isn't.

NULL != 0.  1+2+NULL=NULL.

Try ...
CASE WHEN civ IS NOT NULL THEN civ ELSE '' END ||
CASE WHEN name IS NOT NULL THEN name ELSE '' END ||
CASE WHEN forname IS NOT NULL THEN forname ELSE '' END AS identity

- James