Join the Stack Overflow Community
Stack Overflow is a community of 6.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

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

share|improve this question
    
There are no duplicates in your list. What results do you want? – Gordon Linoff 22 hours ago
up vote 2 down vote

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)
share|improve this answer
    
Thanks that worked! – jimny 11 hours ago
up vote 2 down vote

Another solution without lateral subquery:

select array_agg(distinct val) from
  (select unnest(my_column) as val from my_table) x;
share|improve this answer
    
That worked fine – jimny 11 hours ago

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.