PostgreSQL array aggregate

Interestingly enough, I have only now have found this declaration in the User-Defined Aggregates related Postgres documentation chapter:

CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);

This array aggregate function is very useful when working with arrays in PostgreSQL and it is not included to the default installation (starting from version 8.4 array_agg() function is available). It can be used as a reverse to the ARRAY(query) construct and sometimes together with generate_series() result set generation function.

Another, sometimes quite important, aggregate function to aggregate text is

CREATE AGGREGATE text_accum (text)
(
sfunc = textcat,
stype = text,
initcond = ''
);

but as it does not allow to insert delimiters in the accumulated text it's usage is quite limited.

To accumulate texts using say a comma as a delimiter array_to_string(array_accum(TEXT_COLUMN_TO_AGGREGATE), ', ') construct can be used (starting from version 9.0 a fast string_agg() is available to do that).

To concatenate several arrays in one aggregated array, very simple aggregate can be used

CREATE AGGREGATE array_accum_cat(anyarray) (
SFUNC=array_cat,
STYPE=anyarray,
INITCOND='{}'
);

This makes it possible to merge several arrays together in one one-dimensional array.

One can find more related information in my other post Merging and Manipulating Arrays in PostgreSQL