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

to merge arrays you can use || operator more

to put result in flat list use uunest func

example:

select unnest(ARRAY[1,2] || ARRAY[3,2] || ARRAY[4,5]) as number;

result


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.


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