Try this one. select X.client_id, array_agg(X.color) from (select distinct client_id, unnest(service_codes) as color from foo) X group by X.client_id;
On Tue, Mar 5, 2013 at 3:28 PM, Ken Tanzer <ken.tan...@gmail.com> wrote: > I have a field containing a set of codes in a varchar array, each tied to > a person. > > client_id | integer > | > service_codes | character varying(10)[] | > > I'm trying to query this info so that I can get the list (presumably in an > array) of all the values in this array, across all the records for a > client. So that if a person has two records, one with ORANGE and BLUE, and > one with BLUE and GREEN, I could end up with a list of ORANGE, BLUE and > GREEN. > > I had hopes for: > > SELECT client_id,array_agg(service_codes) FROM foo GROUP BY client_id; > > But was rebuffed with "ERROR: could not find array type for data type > character varying[]" > > There's probably an easy answer for this, but it's completely escaping me. > Any help appreciated. Thanks. > > Ken > > -- > AGENCY Software > A data system that puts you in control > 100% Free Software > *http://agency-software.org/* > ken.tan...@agency-software.org > (253) 245-3801 > > Subscribe to the mailing > list<agency-general-requ...@lists.sourceforge.net?body=subscribe> > to > learn more about AGENCY or > follow the discussion. >