[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 | operatorlawless | 509 | operatorlawless | 509 |
originatorlcalvet | 622 | originatorloomis | 514 | operatorloomis | 514 | originatorpig | 614 |
operatorpig | 614 | originatorpig | 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)
