2008-04-04

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

3 comments:

arc said...

thanks for the explication, very useful :)

Pavel Stěhule said...

atention - these custom aggregates are slow - use buildin array_agg instead when is possible

Valentine Gogichashvili said...

@Pavel

This is a very old post actually :) So now one should definitely use a build-in...

Another thing is, that this method, does not leek memory like some early implementations of the array_agg... and I found recently at least one "conservatively" maintained production system, where the memory leek of array_agg was still an issue (as far as I remember it was a 8.4.1 or even 8.4.0)