Postgres Arrays Rows aggregation

Question

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

There are no duplicates in your list. What results do you want? — Gordon Linoff
– Gordon Linoff, Commented Feb 19, 2017 at 0:32

klin · Accepted Answer · 2017-02-19 00:34:49Z

2

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)

answered Feb 19, 2017 at 0:34

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

max taldykin · Accepted Answer · 2017-02-19 00:39:53Z

2

Another solution without lateral subquery:

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

answered Feb 19, 2017 at 0:39

max taldykin

13k5 gold badges49 silver badges69 bronze badges

Collectives™ on Stack Overflow

Postgres Arrays Rows aggregation

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related