From: | george young <gry(at)ll(dot)mit(dot)edu> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | how to convert relational column to array? |
Date: | 2005-12-19 16:06:12 |
Message-ID: | 20051219110612.26de2268.gry@ll.mit.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-12-19 16:27:30 | Re: Rule causes baffling error |
Previous Message | Richard Huxton | 2005-12-19 16:05:13 | Re: Rule causes baffling error |