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.gauth...@intel.com>wrote: > The select array (select col1 from foo.... ); ...did it. > > Thanks! > > -----Original Message----- > From: Merlin Moncure [mailto:mmonc...@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.gauth...@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 >
