Обсуждение: Using || operator to fold multiple columns into one
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 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 # 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?
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
In response to Bryce Nesbitt : > 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; At least one column contains NULL. To handle that, use coalesce(column,'') to change NULL to ''. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On Wed, Dec 23, 2009 at 1:44 AM, Craig Ringer <craig@postnewspapers.com.au> wrote: > SELECT COALESCE( t_number::text, t:string::text, t_date::text, t_boolean::text) AS value; Your invocation of COALESCE is incorrect -- it is n-ary, but it returns its first non-NULL argument. Instead, try: SELECT COALESCE(t_number::text, '') || COALESCE(t_string::text, '') || COALESCE(t_date::text, '') || COALESCE(t_boolean::text,'') || ... rls -- :wq
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: > >> SELECT COALESCE( t_number::text, t:string::text, t_date::text, t_boolean::text) AS value; > > 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 Ringer
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.
On Tue, Dec 29, 2009 at 4:44 PM, Bryce Nesbitt <bryce2@obviously.com> wrote: > Craig is correct in the OP attempt. All but one field is intended to be null Ah; my misunderstanding, then. Sorry for the noise. > Duplicate rows, or data in multiple columns, would wreck havoc on the > scheme. If there is a better way, I am all eyes. One thought would be to add a couple of constraints: a CHECK constraint to ensure that one and only one of the columns is populated (think "t_number IS NOT NULL AND t_string IS NULL AND t_boolean IS NULL AND t_date IS NULL OR..."), and a UNIQUE constraint against (context_key, t_number, t_string, t_boolean, t_date). That's not really a "better" way, but it should prevent both duplicate and "compound" data. rls -- :wq