From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Brent Wood'" <b(dot)wood(at)niwa(dot)co(dot)nz>, <spluque(at)gmail(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: concatenating with NULLs |
Date: | 2011-04-25 22:52:40 |
Message-ID: | 013501cc039b$7ba2c1e0$72e845a0$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Using a CASE construct is good when you need non-string output but COALESCE
is functionality equivalent and much less verbose when doing a default
string output for null values.
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Brent Wood
Sent: Monday, April 25, 2011 6:33 PM
To: spluque(at)gmail(dot)com; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] concatenating with NULLs
Hi Seb,
Use CASE to change nulls to empty strings (or a placeholder) as below.
See: http://www.postgresql.org/docs/9.0/static/functions-conditional.html
if you want a placeholder in the result to indicate the presence of a null,
try the second SQL:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++
test=# select (case when 'a' isnull then '' else 'a' end) || (case when 'b'
isnull then '' else 'b' end) || (case when NULL is null then '' end);
?column?
----------
ab
(1 row)
test=# select (case when 'a' isnull then '_' else 'a' end) || (case when 'b'
isnull then '_' else 'b' end) || (case when NULL is null then '_' end);
?column?
----------
ab_
(1 row)
test=#
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++
HTH,
Brent Wood
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Seb <spluque(at)gmail(dot)com> 04/26/11 10:21 AM >>>
Hi,
A query such as:
SELECT 'a' || 'b' || NULL;
returns a NULL. How can I ensure I get 'ab' in the result? I'm trying
to concatenate columns and one of them might have NULL values, which I
simply want to ignore.
Cheers,
--
Seb
--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Please consider the environment before printing this email.
NIWA is the trading name of the National Institute of Water & Atmospheric
Research Ltd.
From | Date | Subject | |
---|---|---|---|
Next Message | Phoenix Kiula | 2011-04-26 02:50:49 | Re: Help - corruption issue? |
Previous Message | Seb | 2011-04-25 22:42:47 | Re: concatenating with NULLs |