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

In response to Gauthier, Dave :
> 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.

test=*# select array_agg(col1) from foo;
       array_agg
-----------------------
 {aaa,bbb,ccc,ddd,eee}


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

A. Kretschmer wrote:
> In response to Gauthier, Dave :
>
>> 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.
>>
>
> test=*# select array_agg(col1) from foo;
>        array_agg
> -----------------------
>  {aaa,bbb,ccc,ddd,eee}
>
Or select ARRAY (select col1 from foo);

A. Kretschmer wrote:
> In response to Gauthier, Dave :
>
>> 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.
>>
>
> test=*# select array_agg(col1) from foo;
>        array_agg
> -----------------------
>  {aaa,bbb,ccc,ddd,eee}
>
/me scratches on head - wasn't there something with array?

select ARRAY (select col1 from foo);

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

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

Thanks!

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@gmail.com]
Sent: Tuesday, July 27, 2010 9:25 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.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.gauthier@intel.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

On Fri, Jul 30, 2010 at 10:34 AM, Derrick Rice <derrick.rice@gmail.com> wrote:
> 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.

yup...we've had array() for ages (I think -- see below).

now, you've always been able to do array aggregation in userland --
it's been in the docs as example since I can remember (see here:
http://www.postgresql.org/docs/8.2/static/xaggr.html)

The old school array_accum however is much slower than the newer
array_agg. (which in turn is just a tiny bit slower than array()
discounting grouping effects).  So you _definitely_ want to use
array() if you don't require aggregate grouping features in older
postgres versions.

hm. I looked for the documentation for array(select...) to figure out
exactly when it was introduced, couldn't find it.  Anyone know
if/where this is documented?

merlin

On Fri, Jul 30, 2010 at 11:50 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Fri, Jul 30, 2010 at 10:34 AM, Derrick Rice <derrick.rice@gmail.com> wrote:
>> 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.
>
> yup...we've had array() for ages (I think -- see below).
>
> now, you've always been able to do array aggregation in userland --
> it's been in the docs as example since I can remember (see here:
> http://www.postgresql.org/docs/8.2/static/xaggr.html)
>
> The old school array_accum however is much slower than the newer
> array_agg. (which in turn is just a tiny bit slower than array()
> discounting grouping effects).  So you _definitely_ want to use
> array() if you don't require aggregate grouping features in older
> postgres versions.
>
> hm. I looked for the documentation for array(select...) to figure out
> exactly when it was introduced, couldn't find it.  Anyone know
> if/where this is documented?

I found it -- array() syntax is documented in array_constructor
portion of syntax.sgml.  It's been in postgres at least since 7.4,
which is as far back as I checked.

http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

merlin