Lists: | pgsql-general |
---|
From: | "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | select a list of column values directly into an array |
Date: | 2010-07-27 13:03:35 |
Message-ID: | 482E80323A35A54498B8B70FF2B879800465B3CF95@azsmsx504.amr.corp.intel.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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!
From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: select a list of column values directly into an array |
Date: | 2010-07-27 13:09:39 |
Message-ID: | 20100727130939.GF23002@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Yeb Havinga <yebhavinga(at)gmail(dot)com> |
---|---|
To: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: select a list of column values directly into an array |
Date: | 2010-07-27 13:20:17 |
Message-ID: | 4C4EDD11.2090300@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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);
From: | Yeb Havinga <yebhavinga(at)gmail(dot)com> |
---|---|
To: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: select a list of column values directly into an array |
Date: | 2010-07-27 13:21:15 |
Message-ID: | 4C4EDD4B.9020907@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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);
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 |
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
From: | "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(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:33:04 |
Message-ID: | 482E80323A35A54498B8B70FF2B879800465B3CFC0@azsmsx504.amr.corp.intel.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
The select array (select col1 from foo.... ); ...did it.
Thanks!
-----Original Message-----
From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
Sent: Tuesday, July 27, 2010 9:25 AM
To: Gauthier, Dave
Cc: pgsql-general(at)postgresql(dot)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(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
From: | Derrick Rice <derrick(dot)rice(at)gmail(dot)com> |
---|---|
To: | "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-30 14:34:09 |
Message-ID: | AANLkTi=k7X_PzZ5MY01wL17EZansCOb8FVAVF1hNLHAk@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)gauthier(at)intel(dot)com>wrote:
> The select array (select col1 from foo.... ); ...did it.
>
> Thanks!
>
> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
> Sent: Tuesday, July 27, 2010 9:25 AM
> To: Gauthier, Dave
> Cc: pgsql-general(at)postgresql(dot)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(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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From: | Derrick Rice <derrick(dot)rice(at)gmail(dot)com> |
---|---|
To: | "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-30 14:34:58 |
Message-ID: | AANLkTingEa4miVR2Dj17+w2a4Eq1UKN1pWiicUJ-La7F@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Tue, Jul 27, 2010 at 9:33 AM, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com>wrote:
> The select array (select col1 from foo.... ); ...did it.
>
> Thanks!
>
> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
> Sent: Tuesday, July 27, 2010 9:25 AM
> To: Gauthier, Dave
> Cc: pgsql-general(at)postgresql(dot)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(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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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
From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Derrick Rice <derrick(dot)rice(at)gmail(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-30 15:50:03 |
Message-ID: | AANLkTi=dua5s9vgXDJX70DPwJo+q09B8U1D8uDp6Z3i2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Fri, Jul 30, 2010 at 10:34 AM, Derrick Rice <derrick(dot)rice(at)gmail(dot)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
From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Derrick Rice <derrick(dot)rice(at)gmail(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-31 19:36:43 |
Message-ID: | AANLkTin9c2PTwxWsuWmS0=n+kO8U3Vh-R431O9FwrWJg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Fri, Jul 30, 2010 at 11:50 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Fri, Jul 30, 2010 at 10:34 AM, Derrick Rice <derrick(dot)rice(at)gmail(dot)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