X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id B516E11F8E7E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Apr 2006 20:36:29 -0300 (ADT)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Score: 1.823
X-Spam-Level: *
X-Spam-Status: No, score=1.823 required=5 tests=[RCVD_IN_SORBS_SOCKS=1.823]
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id lQUb82YEcaqI
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Apr 2006 20:35:50 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from canyonero.dot.net.au (canyonero.dot.net.au [202.147.68.14])
	by postgresql.org (Postfix) with ESMTP id 7CB7E11F8E77
	for <pgsql-sql@postgresql.org>; Mon, 10 Apr 2006 19:52:29 -0300 (ADT)
Received: from [203.129.40.123] (helo=ppp-40-123.grapevine.net.au)
	by canyonero.dot.net.au with esmtp (Exim 3.35 #1 (Debian))
	id 1FT5F6-0000hw-00; Tue, 11 Apr 2006 08:52:20 +1000
Subject: Re: concatenation with a null column (using ||) nulls the
From: Ross Johnson <ross.johnson@homemail.com.au>
To: Neil Harkins <nharkins@well.com>
Cc: PostgreSQL SQL List <pgsql-sql@postgresql.org>
In-Reply-To: <Pine.SOL.4.10.10604101430250.2779-100000@well.com>
References: <Pine.SOL.4.10.10604101430250.2779-100000@well.com>
Content-Type: text/plain
Date: Tue, 11 Apr 2006 08:52:15 +1000
Message-Id: <1144709535.8841.440.camel@desk.home>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.4 (2.0.4-7) 
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200604/69
X-Sequence-Number: 24878

On Mon, 2006-04-10 at 14:36 -0700, Neil Harkins wrote:
> inventory=> SELECT cabinets_name, cabinets_description 
> FROM cabinets WHERE cabinets_datacenters = 2;
>  cabinets_name | cabinets_description
> ---------------+----------------------
>  548-4th-Cab2  |
>  548-4th-RR1   |
>  548-4th-RR2   |
>  548-4th-Cab1  |
> (4 rows)
> 
> inventory=> SELECT cabinets_name || ' - ' || cabinets_description AS concat 
> FROM cabinets WHERE cabinets_datacenters = 2;
>      concat
> -----------------
> 
> 
> 
>  548-4th-Cab1 -
> (4 rows)
> 
> Note: The cabinets_description for the "548-4th-Cab1" row is " ", 
> not NULL, hence it being displayed. Is this standard SQL behavior? 
> 

I don't know if this is the "correct SQL" answer, however, in the past,
I've used the COALESCE() function to handle this situation.

<quote>
COALESCE(value [, ...])

The COALESCE function returns the first of its arguments that is not
null. Null is returned only if all arguments are null. This is often
useful to substitute a default value for null values when data is
retrieved for display, for example: 
SELECT COALESCE(description, short_description, '(none)') ...

Like a CASE expression, COALESCE will not evaluate arguments that are
not needed to determine the result; that is, arguments to the right of
the first non-null argument are not evaluated. 
</quote>

Regards.

> Client is from rpm: postgresql-8.0.7-1.FC4.1
> Server is from rpm: postgresql-server-8.0.7-1.FC4.1
> 
> -neil
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
>