From: Bryce Nesbitt <bryce2@obviously.com>
To: sql pgsql <pgsql-sql@postgresql.org>
Cc: Craig Ringer <craig@postnewspapers.com.au>
Cc: Rosser Schwarz <rosser.schwarz@gmail.com>
Subject: Re: Using || operator to fold multiple columns into one
Date: Tue, 29 Dec 2009 15:44:08 -0800
Message-ID: <4B3A9448.20308@obviously.com> (raw)
In-Reply-To: <4B32DB14.6080308@postnewspapers.com.au>
References: <4B31D619.1090706@obviously.com>
<4B31D86D.9030000@postnewspapers.com.au>
<37d451f70912231304i3515e575x69b31638962739a6@mail.gmail.com>
<4B32DB14.6080308@postnewspapers.com.au>
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.
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: bryce2@obviously.com, craig@postnewspapers.com.au, rosser.schwarz@gmail.com
Subject: Re: Using || operator to fold multiple columns into one
In-Reply-To: <4B3A9448.20308@obviously.com>
* 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