Re: Populate arrays from multiple rows

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(at)gmail(dot)com]
Sent: Wednesday, April 28, 2010 4:33 PM
To: Clift, Robert
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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

--
For up-to-date information about H1N1 Swine Flu visit http://www.myflusafety.com or call 877-352-3581