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?
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);
with ordinality for that.
distinct before the unnest.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.
O(n^2) and so isn't suitable for large datasets. source: I used it on some large datasets in production and had to rip it out =)O(n^2), are you sure? It copies the whole array once per iteration, including all prior members, when it constructs a new one. Either way, it'll still be plenty slow for long inputs.O(n^2) IIRC.numpy or intarray) or use C to do it more efficiently.
string_to_array(string_agg(array_to_string(column_name ,','),','),',')
This can be some clue for your situation. I've done like this.
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;
To enrich Craig Ringer's answer, his CREATE AGGREGATE solution is good but reportedly slow. That's because it only has an SFUNC defined, which means it can only aggregate a single item into the array, one at a time.
This can be improved by defining the aggregate as a partial aggregate. In this mode, it can collect sub-arrays -- possibly in parallel -- then merge those arrays into larger arrays. This is done by adding a COMBINEFUNC definition, which in this case is also just array_cat. I also mark the function as parallel safe.
CREATE AGGREGATE public.array_agg_flat(anycompatiblearray) (
SFUNC = array_cat,
COMBINEFUNC = array_cat,
STYPE = anycompatiblearray,
PARALLEL = SAFE
);
This should drastically reduce the number of array (re)allocations occurring behind the scenes.
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.
