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}

/me scratches on head - wasn't there something with array?

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}

Or 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

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

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

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.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

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