Search code examples
sqlarrayspostgresql

How to merge all integer arrays from all records into single array in postgres

I have a column which is of type integer array. How can I merge all of them into a single integer array?

For example: If I execute query: 

select column_name from table_name

I get result set as:

-[RECORD 1]----------
column_name | {1,2,3}
-[RECORD 2]----------
column_name | {4,5}

How can I get {1,2,3,4,5} as final result?

Solution

  • You could use unnest to open up the arrays and then array_agg to put them back together:

    select array_agg(c)
from (
  select unnest(column_name)
  from table_name
) as dt(c);