Обсуждение: 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

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