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

The only way you can do this is inside a function:

CREATE FUNCTION merge_arrays() RETURNS int[] AS $$
DECLARE
  this record;
  res  int[];
BEGIN
  FOR this IN
    SELECT column_name FROM table_name
  LOOP
    array_cat(res, this.column_name);
  END LOOP;
  RETURN res;
END; $$ LANGUAGE plpgsql;

Then you can

SELECT merge_arrays();

to get the result you are looking for.

This of course hard-codes your table definition into the function, which may (or may not) be an issue. In addition, you may want to put a WHERE clause in the loop query to restrict the records whose arrays you want to append; you might use an additional function parameter to do this.

Keep in mind that you might get a really large array as your table increases in size and that may affect performance. Do you really need all sub-arrays from all records in one large array? Have a look at your application and see if you can do the merge at that level, rather than in a single query.