Postgres: array_agg throws 'cannot accumulate empty arrays' for empty array(s)

Question

Having problems when using array_agg when a row contains an empty array ({}).

This is my SQL query:

SELECT service_name, metric_name, array_agg(value_textarray)
FROM service_data
WHERE service_name = 'ActivityDataService'
GROUP BY service_name, metric_name

Where the column definitions are the following:

service_name - text
metric_name - text
value_textarray - text[]

When I execute the query and I have empty array ({}) in the database, I get the following error:

ERROR:  cannot accumulate empty arrays

How should I go about fixing this?

Answer 1

I had the same issue where I couldn't filter the empty array and I found this function. This function avoids the 'accumulating empty arrays' error.

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

source: https://gist.github.com/ryandotsmith/4602274