The select array (select col1 from foo.... ); ...did it. Thanks! -----Original Message----- From: Merlin Moncure [mailto:mmoncure@xxxxxxxxx] Sent: Tuesday, July 27, 2010 9:25 AM To: Gauthier, Dave Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: select a list of column values directly into an array On Tue, Jul 27, 2010 at 9:03 AM, Gauthier, Dave <dave.gauthier@xxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
