[GENERAL] How to get array of unique array values across rows?

2013-03-05 Thread Ken Tanzer 
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
listagency-general-requ...@lists.sourceforge.net?body=subscribe
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] How to get array of unique array values across rows?

2013-03-05 Thread Ken Tanzer 
That worked perfectly.  Thanks a lot!

On Tue, Mar 5, 2013 at 12:49 PM, ChoonSoo Park luisp...@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.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 
 listagency-general-requ...@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.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing
listagency-general-requ...@lists.sourceforge.net?body=subscribe
 to
learn more about AGENCY or
follow the discussion.