Message-ID: <4B3A9448.20308@obviously.com>
Date: Tue, 29 Dec 2009 15:44:08 -0800
From: Bryce Nesbitt <bryce2@obviously.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.8.1.22) Gecko/20090605 SeaMonkey/1.1.17
	(Ubuntu-1.1.17+nobinonly-0ubuntu0.8.04.1)
MIME-Version: 1.0
To: sql pgsql <pgsql-sql@postgresql.org>
CC: Craig Ringer <craig@postnewspapers.com.au>,
	Rosser Schwarz <rosser.schwarz@gmail.com>
Subject: Re: Using || operator to fold multiple columns into one
References: <4B31D619.1090706@obviously.com>	
	<4B31D86D.9030000@postnewspapers.com.au>
	<37d451f70912231304i3515e575x69b31638962739a6@mail.gmail.com>
	<4B32DB14.6080308@postnewspapers.com.au>
In-Reply-To: <4B32DB14.6080308@postnewspapers.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Craig Ringer wrote:
> On 24/12/2009 5:04 AM, Rosser Schwarz wrote:
>> On Wed, Dec 23, 2009 at 1:44 AM, Craig Ringer
>> <craig@postnewspapers.com.au>  wrote:
>> Your invocation of COALESCE is incorrect -- it is n-ary, but it
>> returns its first non-NULL argument.
> Yes. That was the point.
>
> I think we're assuming the OP wants different things. You're assuming 
> they're trying to concatenate all fields, where more than one field in 
> a given record may be non-null. I'm assuming that all but one field in 
> each record will be null, and they want to show the "value" of the 
> record - in other words, they're using the record as a sort of union 
> type. It looks like that from the example they gave.

Craig is correct in the OP attempt.   All but one field is intended to 
be null, and the union is simply  to get a more compact output at the 
psql prompt (without invoking "/pset format=wrapped" ).  The union will 
be used only at the command prompt.

Craig's example works with one typo fix, thanks!:
select context_key,keyname,COALESCE( t_number::text, t_string::text, 
t_date::text, t_boolean::text) AS value from context_keyvals;


This table was designed to allow arbitrary key value data, allowing 
postgres type operations on the data, yet still allowing clustering on 
the index:

# (select context_key from context_keyvals where keyname='BOGUS' and 
t_number > 5);

# SELECT contexts.context_key FROM contexts
JOIN context_keyvals ON (context_keyvals.context_key=contexts.context_key)
WHERE contexts.site_key = 4130
AND (context_keyvals.t_string LIKE 'T%' AND 
context_keyvals.keyname='SHORT_TITLE');

# \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


Duplicate rows, or data in multiple columns, would wreck havoc on the 
scheme.  If there is a better way, I am all eyes.