Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 47D27475C29
	for <pgsql-sql@postgresql.org>; Tue,  4 Jun 2002 10:40:42 -0400 (EDT)
Received: from shire.ontko.com (shire.ontko.com [199.164.165.1])
	by postgresql.org (Postfix) with ESMTP id 8FACC475A0B
	for <pgsql-sql@postgresql.org>; Tue,  4 Jun 2002 10:40:40 -0400 (EDT)
Received: from nick (bilbo.ontko.com [199.164.165.101])
	by shire.ontko.com (8.9.3/8.9.3/Debian 8.9.3-21) with SMTP id JAA08946;
	Tue, 4 Jun 2002 09:40:34 -0500
X-Authentication-Warning: shire.ontko.com: Host bilbo.ontko.com
	[199.164.165.101] claimed to be nick
Reply-To: <nickf@ontko.com>
From: "Nick Fankhauser" <nickf@ontko.com>
To: "David BOURIAUD" <david.bouriaud@ac-rouen.fr>, <pgsql-sql@postgresql.org>
Subject: Re: Is this a feature ?
Date: Tue, 4 Jun 2002 09:38:53 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGIEBLFBAA.nickf@ontko.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0)
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
In-Reply-To: <200206041018.24328.david.bouriaud@ac-rouen.fr>
Importance: Normal
X-Archive-Number: 200206/27
X-Sequence-Number: 7992

> 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 !!!!

The crux of the matter is that zero and null aren't the same thing. Null
means in essence "We don't know", so this equation is better writtin as:

1+2+"don't know" = "don't know"

> is there a
> way to work
> this around ?

It *is* often true that when concatenating strings, we want to tell SQL "if
this value is null, treat it like an empty string". This is how you would do
it:

coalesce( civ,'')||coalesce(name,'')||coalesce(forname,'')

Regards,

-Nick