Re: 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(dot)tanzer(at)gmail(dot)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(dot)tanzer(at)agency-software(dot)org
> (253) 245-3801
>
> Subscribe to the mailing list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
> to
> learn more about AGENCY or
> follow the discussion.
>