From: Ross Johnson <ross.johnson@homemail.com.au>
To: Neil Harkins <nharkins@well.com>
Cc: PostgreSQL SQL List <pgsql-sql@postgresql.org>
Subject: Re: concatenation with a null column (using ||) nulls the
Date: Tue, 11 Apr 2006 08:52:15 +1000
Message-ID: <1144709535.8841.440.camel@desk.home> (raw)
In-Reply-To: <Pine.SOL.4.10.10604101430250.2779-100000@well.com>
References: <Pine.SOL.4.10.10604101430250.2779-100000@well.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ross.johnson@homemail.com.au, nharkins@well.com
Subject: Re: concatenation with a null column (using ||) nulls the
In-Reply-To: <1144709535.8841.440.camel@desk.home>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox