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
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)
Another solution without lateral subquery:
select array_agg(distinct val) from
(select unnest(my_column) as val from my_table) x;
