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

[Derrick Rice <derrick.rice@xxxxxxxxx>]

On Tue, Jul 27, 2010 at 9:33 AM, Gauthier, Dave <dave.gauthier@xxxxxxxxx> wrote:

The select array (select col1 from foo.... );  ...did it.

Thanks!

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@xxxxxxxxx]
Sent: Tuesday, July 27, 2010 9:25 AM
To: Gauthier, Dave
Cc: pgsql-general@xxxxxxxxxxxxxx

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

On Tue, Jul 27, 2010 at 9:03 AM, Gauthier, Dave <dave.gauthier@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
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