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-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Brent Wood Sent: Monday, April 25, 2011 6:33 PM To: splu...@gmail.com; pgsql-general@postgresql.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 <splu...@gmail.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@postgresql.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.
