How to merge all integer arrays from all records into single array in postgres
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 lateral subquery for that:
select array_agg(u.a)from (values (array[1, 2, 3]), (array[4, 5])) t (a) join lateral unnest(t.a) u (a) on true;