CODEWITHSUNDEEP
arrayspostgresqlaggregation
jimny
jimny

Reputation: 103

Postgres Arrays Rows aggregation

I have a recursive query in which i m getting rows of arrays as shown below. How could I possible merge all rows into one array in one row and removing duplicates? Ordering is not important. 

--my_column--
"{431}"
"{431,33}"
"{431,60}"
"{431,28}"
"{431,1}"
"{431,226}"
"{431,38}"
"{431,226,229}"
"{431,226,227}"
"{431,226,235}"
"{431,226,239}"
"{431,226,241}"

I tried the query below but I am getting one empty integer [] column

select array(select unnest(my_column) from my_table

thanks

Upvotes: 0

Views: 72

Answers (2)

max taldykin
max taldykin

Reputation: 12908

Another solution without lateral subquery:

select array_agg(distinct val) from
  (select unnest(my_column) as val from my_table) x;

Upvotes: 2

klin
klin

Reputation: 121889

Use array_agg() with distinct and (not necessary) order by from unnest():

with my_table(my_column) as (
values
    ('{431}'::int[]),
    ('{431,33}'),
    ('{431,60}'),
    ('{431,28}'),
    ('{431,1}'),
    ('{431,226}'),
    ('{431,38}'),
    ('{431,226,229}'),
    ('{431,226,227}'),
    ('{431,226,235}'),
    ('{431,226,239}'),
    ('{431,226,241}')
)

select array_agg(distinct elem order by elem)
from my_table,
lateral unnest(my_column) elem;

                  array_agg                  
---------------------------------------------
 {1,28,33,38,60,226,227,229,235,239,241,431}
(1 row)

Upvotes: 2

Related Questions