Re: concatenation with a null column (using ||) nulls the result?

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Neil Harkins <nharkins(at)well(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: concatenation with a null column (using ||) nulls the result?
Date: 2006-04-10 21:55:56
Message-ID: 20060410215556.GB5856@surnet.cl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Terry Lee Tucker 2006-04-10 22:03:53 Re: concatenation with a null column (using ||) nulls the result?
Previous Message Tom Lane 2006-04-10 21:47:26 Re: concatenation with a null column (using ||) nulls the result?