Postgres Pro
Downloads
Home   >   mailing lists

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

From Alvaro Herrera
Subject Re: concatenation with a null column (using ||) nulls the result?
Date
Msg-id 20060410215556.GB5856@surnet.cl
Whole thread Raw
In response to concatenation with a null column (using ||) nulls the result?  (Neil Harkins <nharkins@well.com>)
Responses Re: concatenation with a null column (using ||) nulls the result?  (Terry Lee Tucker <terry@esc1.com>)
List pgsql-sql
Tree view 
Neil Harkins wrote:

> Note: The cabinets_description for the "548-4th-Cab1" row is " ", 
> not NULL, hence it being displayed. Is this standard SQL behavior? 

Yes; something || NULL yields NULL.  If you want NULL to behave as ""
for the purpose of the concatenation, try

SELECT cabinets_name || ' - ' || COALESCE(cabinets_description, '') AS concat 
FROM cabinets WHERE cabinets_datacenters = 2;

I'm assuming cabinets_name is NOT NULL, so it doesn't need COALESCE.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: concatenation with a null column (using ||) nulls the result?
Next
From: Terry Lee Tucker
Date:
Subject: Re: concatenation with a null column (using ||) nulls the result?