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@gmail.com]
Sent: Wednesday, April 28, 2010 4:33 PM
To: Clift, Robert
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Populate arrays from multiple rows
On Wed, Apr 28, 2010 at 1:39 PM, <Robert_Clift@doh.state.fl.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