Message-ID: <4B31D619.1090706@obviously.com>
Date: Wed, 23 Dec 2009 00:34:33 -0800
From: Bryce Nesbitt <bryce2@obviously.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.8.1.23) Gecko/20090410 SUSE/1.1.18-0.1 SeaMonkey/1.1.18
MIME-Version: 1.0
To: sql pgsql <pgsql-sql@postgresql.org>
Subject: Using || operator to fold multiple columns into one
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Dear experts,

This point is confusing me with the || operator. I've got a table with
"one column per data type", like so:

# \d context_keyvals;
            Table "public.context_keyvals"
   Column    |            Type             | Modifiers
-------------+-----------------------------+-----------
 context_key | integer                     | not null
 keyname     | text                        |
 t_number    | integer                     |
 t_string    | text                        |
 t_boolean   | boolean                     |
 t_date      | timestamp without time zone |
Indexes:
    "context_keyvals_ck" btree (context_key) CLUSTER
Foreign-key constraints:
    "context_keyvals_context_key_fkey" FOREIGN KEY (context_key)
REFERENCES contexts(context_key) ON DELETE CASCADE
# select version()
PostgreSQL 8.3.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Debian 4.3.2-1.1) 4.3.2

------------------------------
Just for pretty sake I'd like to be able to use psql to view it like 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.  I can use the
|| operator to concatenate strings:
# select '--'||t_number::text from context_keyvals;

But the moment I try to combine columns, the result is blank.
# select '--'||t_number::text||t_string::text from context_keyvals;

What's up?