Обсуждение: select a list of column values directly into an array
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.
Thanks in advance for any help!
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
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
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:
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.orgSubject: 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
On Tue, Jul 27, 2010 at 9:33 AM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
(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
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.orgSubject: 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