Re: concatenation with a null column (using ||) nulls the - Mailing list pgsql-sql

From Ross Johnson
Subject Re: concatenation with a null column (using ||) nulls the
Date
Msg-id 1144709535.8841.440.camel@desk.home
Whole thread Raw
In response to concatenation with a null column (using ||) nulls the result?  (Neil Harkins <nharkins@well.com>)
List pgsql-sql
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
> 



pgsql-sql by date:

Previous
From: Terry Lee Tucker
Date:
Subject: Re: concatenation with a null column (using ||) nulls the result?
Next
From: Ross Johnson
Date:
Subject: Re: how to use recursion to find end nodes of a tree