From: Craig Ringer <craig@postnewspapers.com.au>
To: Bryce Nesbitt <bryce2@obviously.com>
Cc: sql pgsql <pgsql-sql@postgresql.org>
Subject: Re: Using || operator to fold multiple columns into one
Date: Wed, 23 Dec 2009 16:44:29 +0800
Message-ID: <4B31D86D.9030000@postnewspapers.com.au> (raw)
In-Reply-To: <4B31D619.1090706@obviously.com>
References: <4B31D619.1090706@obviously.com>
On 23/12/2009 4:34 PM, Bryce Nesbitt wrote:
> Dear experts,
>
> This point is confusing me with the || operator. I've got a table with
> "one column per data type", like so:
Dare I ask why? What problem are you trying to solve by doing this?
> # select context_key,keyname,t_number||t_string||t_date||t_boolean as
> value from context_keyvals;
>
> But it is not working, the columns always come up empty.
Here's why:
psql> SELECT ('TEST'||NULL) IS NULL;
?column?
t
--------
(1 row)
`||' applied to anything and null returns null, since it is unknown what
the "null" part of the expression. It makes sense if you think of null
as "unknown" or "undefined".
In your case, you should probably typecast each field to `text' and use
`coalesce' to show the first non-null one. Alternately, you could use a
CASE statement, eg:
SELECT COALESCE( t_number::text, t:string::text, t_date::text,
t_boolean::text) AS value;
Also: You do have a CHECK constraint on the table that asserts that at
most one of those entries may be non-null, right? If not, you're very
likely to land up with entries with more than one t_something non-null
sooner or later so I suggest adding one.
--
Craig Ringer
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: craig@postnewspapers.com.au, bryce2@obviously.com
Subject: Re: Using || operator to fold multiple columns into one
In-Reply-To: <4B31D86D.9030000@postnewspapers.com.au>
* 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