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 nullkeyname
|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.
