pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to convert relational column to array?
3+ messages / 2 participants
[nested] [flat]
* how to convert relational column to array?
@ 2005-12-19 16:06 george young <gry@ll.mit.edu>
2005-12-19 16:54 ` Re: how to convert relational column to array? Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 3+ messages in thread
From: george young @ 2005-12-19 16:06 UTC (permalink / raw)
To: pgsql-sql
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
I'm trying to convert a column from a traditional relational form to an array:
create table old_tab(name text, id int, permits text);
newschm3=# select * from old_tab order by name;
name | id | permits
----------+-------+------------
baker | 581 | operator
lawless | 509 | operator
lawless | 509 | originator
lcalvet | 622 | originator
loomis | 514 | operator
loomis | 514 | originator
pig | 614 | operator
pig | 614 | originator
pig | 614 | supervisor
create table new_tab(name text, id int, permits text[]);
-- I insert one row per name:
insert into new_tab select distinct name,id,cast('{}' as text[]) from old_tab;
Now I want to fold all the 'permits' values into the new permits arrays.
I can do:
update new_tab set permits=new_tab.permits||ot.permits from old_tab ot where ot.name=new_tab.name and ot.permits!=all(new_tab.permits);
but this only gets one permits value per name. Repeating this many times would
eventually get all of them, but it seems there must be a more reliable way?
[I don't care about the *order* of permits values in the array, since order did not exist in old_tab]
Just to be clear, I want to end up with:
newschm3=# select * from new_tab order by name;
name | id | permits
---------+-----+------------------------------------------
baker | 581 | {operator}
lawless | 509 | {operator,originator}
lcalvet | 622 | {originator}
loomis | 514 | {operator,originator}
pig | 614 | {operator,originator,supervisor}
-- George Young
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to convert relational column to array?
2005-12-19 16:06 how to convert relational column to array? george young <gry@ll.mit.edu>
@ 2005-12-19 16:54 ` Michael Fuhr <mike@fuhr.org>
2005-12-19 17:12 ` Re: how to convert relational column to array? george young <gry@ll.mit.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2005-12-19 16:54 UTC (permalink / raw)
To: george young <gry@ll.mit.edu>; +Cc: pgsql-sql
On Mon, Dec 19, 2005 at 11:06:12AM -0500, george young wrote:
> create table new_tab(name text, id int, permits text[]);
>
> -- I insert one row per name:
> insert into new_tab select distinct name,id,cast('{}' as text[]) from old_tab;
>
> Now I want to fold all the 'permits' values into the new permits arrays.
In PostgreSQL 7.4 and later you can build an array from a select,
so I think the following update should work (it did for me when I
tested it):
UPDATE new_tab SET permits = array(
SELECT permits
FROM old_tab
WHERE old_tab.name = new_tab.name AND old_tab.id = new_tab.id
);
--
Michael Fuhr
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how to convert relational column to array?
2005-12-19 16:06 how to convert relational column to array? george young <gry@ll.mit.edu>
2005-12-19 16:54 ` Re: how to convert relational column to array? Michael Fuhr <mike@fuhr.org>
@ 2005-12-19 17:12 ` george young <gry@ll.mit.edu>
0 siblings, 0 replies; 3+ messages in thread
From: george young @ 2005-12-19 17:12 UTC (permalink / raw)
To: pgsql-sql
On Mon, 19 Dec 2005 09:54:49 -0700
Michael Fuhr <mike@fuhr.org> threw this fish to the penguins:
> On Mon, Dec 19, 2005 at 11:06:12AM -0500, george young wrote:
> > create table new_tab(name text, id int, permits text[]);
> >
> > -- I insert one row per name:
> > insert into new_tab select distinct name,id,cast('{}' as text[]) from old_tab;
> >
> > Now I want to fold all the 'permits' values into the new permits arrays.
>
> In PostgreSQL 7.4 and later you can build an array from a select,
> so I think the following update should work (it did for me when I
> tested it):
>
> UPDATE new_tab SET permits = array(
> SELECT permits
> FROM old_tab
> WHERE old_tab.name = new_tab.name AND old_tab.id = new_tab.id
> );
That's exactly what I needed. Works great.
Thanks,
George
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-12-19 17:12 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-19 16:06 how to convert relational column to array? george young <gry@ll.mit.edu>
2005-12-19 16:54 ` Michael Fuhr <mike@fuhr.org>
2005-12-19 17:12 ` george young <gry@ll.mit.edu>
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