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)
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}
