Re: Populate arrays from multiple rows

On Wed, Apr 28, 2010 at 1:39 PM, <Robert_Clift(at)doh(dot)state(dot)fl(dot)us> 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