pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing || operator to fold multiple columns into one
7+ messages / 4 participants
[nested] [flat]
* Using || operator to fold multiple columns into one
@ 2009-12-23 08:34 Bryce Nesbitt <bryce2@obviously.com>
0 siblings, 2 replies; 7+ messages in thread
From: Bryce Nesbitt @ 2009-12-23 08:34 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using || operator to fold multiple columns into one
@ 2009-12-23 08:44 Craig Ringer <craig@postnewspapers.com.au>
parent: Bryce Nesbitt <bryce2@obviously.com>
1 sibling, 1 reply; 7+ messages in thread
From: Craig Ringer @ 2009-12-23 08:44 UTC (permalink / raw)
To: Bryce Nesbitt <bryce2@obviously.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using || operator to fold multiple columns into one
@ 2009-12-23 08:50 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Bryce Nesbitt <bryce2@obviously.com>
1 sibling, 0 replies; 7+ messages in thread
From: A. Kretschmer @ 2009-12-23 08:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using || operator to fold multiple columns into one
@ 2009-12-23 21:04 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 7+ messages in thread
From: Rosser Schwarz @ 2009-12-23 21:04 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: Bryce Nesbitt <bryce2@obviously.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using || operator to fold multiple columns into one
@ 2009-12-24 03:08 Craig Ringer <craig@postnewspapers.com.au>
parent: Rosser Schwarz <rosser.schwarz@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Craig Ringer @ 2009-12-24 03:08 UTC (permalink / raw)
To: Rosser Schwarz <rosser.schwarz@gmail.com>; +Cc: Bryce Nesbitt <bryce2@obviously.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using || operator to fold multiple columns into one
@ 2009-12-29 23:44 Bryce Nesbitt <bryce2@obviously.com>
parent: Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 7+ messages in thread
From: Bryce Nesbitt @ 2009-12-29 23:44 UTC (permalink / raw)
To: pgsql-sql; +Cc: Craig Ringer <craig@postnewspapers.com.au>; Rosser Schwarz <rosser.schwarz@gmail.com>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using || operator to fold multiple columns into one
@ 2009-12-31 16:23 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: Bryce Nesbitt <bryce2@obviously.com>
0 siblings, 0 replies; 7+ messages in thread
From: Rosser Schwarz @ 2009-12-31 16:23 UTC (permalink / raw)
To: Bryce Nesbitt <bryce2@obviously.com>; +Cc: pgsql-sql; Craig Ringer <craig@postnewspapers.com.au>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2009-12-31 16:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-12-23 08:34 Using || operator to fold multiple columns into one Bryce Nesbitt <bryce2@obviously.com>
2009-12-23 08:44 ` Craig Ringer <craig@postnewspapers.com.au>
2009-12-23 21:04 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2009-12-24 03:08 ` Craig Ringer <craig@postnewspapers.com.au>
2009-12-29 23:44 ` Bryce Nesbitt <bryce2@obviously.com>
2009-12-31 16:23 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2009-12-23 08:50 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
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