current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite
1

I have a table full of records and an array column, and now I want to get a list of all (unique) values in that column. What's the best way of getting that?

I tried playing around with unnest, array_agg and string_agg but didn't get anywhere...

(using Postgres 9.2)

share|improve this question
    
Updated answer after rereading you question. – Clodoaldo Neto Jul 15 '13 at 13:44
up vote 2 down vote accepted 
select distinct unnest(a)
from (values
    (array[1, 2]),
    (array[2, 3])
) s(a);
 unnest 
--------
      3
      1
      2

Or aggregated in an array:

select array_agg(a order by a)
from (
    select distinct unnest(a) as a
    from (values
        (array[1, 2]),
        (array[2, 3])
    ) s(a)
) s;
 array_agg 
-----------
 {1,2,3}
share|improve this answer
    
So there is no way to do this in one go or without a subquery? – Manuel Meurer Jul 15 '13 at 14:25
    
@Manuel Not without creating an special aggregate function. – Clodoaldo Neto Jul 15 '13 at 14:36
    
I looked at your first version and actually that is what I need (one row per result). Could you update your post once more and include both versions, for future readers? Thanks! – Manuel Meurer Jul 15 '13 at 19:09
1  
@Manuel Updated – Clodoaldo Neto Jul 15 '13 at 19:12
    
Sweet, thanks!... – Manuel Meurer Jul 15 '13 at 19:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.