How to get array of unique array values across rows?

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.tanzer@gmail.com> wrote:

That worked perfectly. Thanks a lot!

On Tue, Mar 5, 2013 at 12:49 PM, ChoonSoo Park <luispark@gmail.com> wrote:

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.tanzer@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.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list<agency-general-request@lists.sourceforge.net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.

--
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request@lists.sourceforge.net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.