how to convert relational column to array?
От | george young |
---|---|
Тема | how to convert relational column to array? |
Дата | |
Msg-id | 20051219110612.26de2268.gry@ll.mit.edu обсуждение исходный текст |
Ответы |
Re: how to convert relational column to array?
|
Список | 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 | 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)
В списке pgsql-sql по дате отправления: