Thanks Merlin: I failed to mention that I'm running 8.3 (no array_agg), but you certainly pointed me in the right direction. This worked: INSERT INTO foo_arrays SELECT cde, nbr, ARRAY_ACCUM(CAST(aaa AS text)), ARRAY_ACCUM(CAST(bbb AS text)), ARRAY_ACCUM(CAST(ccc AS text)) FROM raw_foo GROUP BY 1,2; Cheers, Rob -----Original Message----- From: Merlin Moncure [mailto:mmoncure@xxxxxxxxx] Sent: Wednesday, April 28, 2010 4:33 PM To: Clift, Robert Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Populate arrays from multiple rows 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 -- For up-to-date information about H1N1 Swine Flu visit http://www.myflusafety.com or call 877-352-3581 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
