Re: select a list of column values directly into an array

On Tue, Jul 27, 2010 at 9:33 AM, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com>wrote:

> The select array (select col1 from foo.... ); ...did it.
>
> Thanks!
>
> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
> Sent: Tuesday, July 27, 2010 9:25 AM
> To: Gauthier, Dave
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] select a list of column values directly into an
> array
>
> On Tue, Jul 27, 2010 at 9:03 AM, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com>
> wrote:
> > Is there a way to select a list of column values directly into an array?
> >
> > create table foo (col1 text);
> >
> > insert into foo (col1) values ('aaa'),('bbb'),('ccc'),('ddd'),('eee');
> >
> > I'd like to load up an array with {aaa,bbb,ccc,ddd,eee}, prefferably with
> a
> > single select statement.
>
> There are basically four ways to create an array:
>
> *) text in:
> select '{1,2,3,4,5}'::int[];
>
> obviously not the best method: use it for example when you need to
> parameterize a query from a client that doesn't understand pgsql
> natives natively (which is basically all of them).
>
> *) list of scalars:
> select array[1,2,3,4,5];
>
> use that when you have a known list of constants you want of feed to a
> query. better version of the above, but it can be awkward if you
> parameterize your queries
>
> *) array syntax construct
> select array(select col from foo);
>
> takes the result of any query and arrayifies it. you can also 'stack'
> arrays, even using full types:
>
> select array
> (
> select row
> (
> foo,
> (
> array(select bar from bar where bar.foo_id = foo.foo_id)
> )
> ) from foo
> );
>
> it's advisable to use declared composite types when doing really fancy
> stuff with this...
>
> *) array_agg
>
> aggregates a column 'in query' using grouping rules. I would only
> advise this when you want to make use of 'group by'.
>
> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

(sorry - I totally top posted on that last reply. Looks like we bottom post
here. Resending bottom-posted)

Is it possible to use the ARRAY(select ...) syntax as a substitute for
array_agg on versions of postgresql that don't have it? (8.2) It works
simply enough when only selecting a single column, but if I need to group by
some other column, I'm not clear how I'd go about doing that.

For example, write the following in ARRAY(select...) form.

select name, array_agg(relative)
from members
group by name

Thanks,

Derrick