Thread: Populate arrays from multiple rows
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.
Cheers,
Rob
For up-to-date information about H1N1 Swine Flu visit http://www.myflusafety.com or call 877 352 3581
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
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