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

Question

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?

Answer 1

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); 

Answer 2

Define a trivial custom aggregate:

CREATE AGGREGATE array_cat_agg(anyarray) (   SFUNC=array_cat,   STYPE=anyarray ); 

and use it:

WITH v(a) AS ( VALUES (ARRAY[1,2,3]), (ARRAY[4,5,6,7])) SELECT array_cat_agg(a) FROM v; 

If you want a particular order, put it within the aggregate call, i.e. array_cat_agg(a ORDER BY ...)

This is roughly O(n log n) for n rows (I think) O(n²) so it is unsuitable for long sets of rows. For better performance you'd need to write it in C, where you can use the more efficient (but horrible to use) C API for PostgreSQL arrays to avoid re-copying the array each iteration.