CODEWITHSUNDEEP
arrayspostgresql
Manuel Meurer
Manuel Meurer

Reputation: 3488

How to aggregate all array values of multiple records in Postgres array?

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)

Upvotes: 2

Views: 1873

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125504

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}

Upvotes: 6

Related Questions