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

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: select a list of column values directly into an array
Date: 2010-07-27 13:25:23
Message-ID: AANLkTimp1K0OposYQCR8dSXRscFRu3yBDi+Zhc0W3ohO@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gauthier, Dave 2010-07-27 13:33:04 Re: select a list of column values directly into an array
Previous Message Yeb Havinga 2010-07-27 13:21:15 Re: select a list of column values directly into an array