On Wed, Apr 28, 2010 at 1:39 PM, <Robert_Clift@xxxxxxxxxxxxxxx> wrote: > Good afternoon: > > I would like to insert some (1 or more) values from multiple rows of one > table into an array in another table. Here's the scenario: > > --table to house data provided by a third party > CREATE TABLE raw_foo ( > rf_id serial PRIMARY KEY, > cde character varying(4), > nbr integer, > aaa character varying(60), > bbb character(10), > ccc character varying(20) > ); > > --table raw_foo populated by copying from a text file > --columns cde||nbr identify a person while columns aaa||bbb||ccc describe an > attribute of a person > --since each person can have one or more attributes, the cde||nbr identifier > is not distinct > --need data in raw_foo flattened so that there is only one record per person > > --second table in which aaa, bbb, and ccc are array fields > CREATE TABLE foo_arrays ( > cde character varying(4), > nbr integer, > aaa text[], > bbb text[], > ccc text[], > PRIMARY KEY (cde, nbr) > ); > > --insertion of all distinct cde||nbr combinations from raw_foo > INSERT INTO foo_arrays > (cde, nbr) > (SELECT cde, nbr > FROM raw_foo > GROUP BY cde, nbr > HAVING COUNT(*) = 1) > UNION > (SELECT cde, nbr > FROM raw_foo > GROUP BY cde, nbr > HAVING COUNT(*) > 1); > > --hope to update foo_arrays.aaa by selecting every instance of raw_foo.aaa > where raw_foo.cde||raw_foo.nbr matches the distinct value of > foo_arrays.cde||foo_arrays.nbr (repeating the process for foo_arrays.bbb and > foo_arrays.ccc) > > UPDATE foo_arrays > SET aaa = ??? > > This is where I'm stumped. > Am I on the right path? > Thanks in advance. Hello, fellow Floridian! :-) how about this: insert into foo_arrays select cde, nbr, array_agg(aaa), array_agg(bbb), array_agg(ccc) group by 1,2; merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
