Re: Using || operator to fold multiple columns into one

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