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