CODEWITHSUNDEEP
sqlpostgresql
Tarps
Tarps

Reputation: 1933

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

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?

Upvotes: 13

Views: 13814

Answers (2)

iserdmi
iserdmi

Reputation: 103

SELECT service_name, metric_name, array_agg(value_textarray) filter(where value_textarray <> '{}')
FROM service_data
WHERE service_name = 'ActivityDataService'
GROUP BY service_name, metric_name

You also can add filter(where value_textarray <> '{}') right after array_agg(value_textarray)

Upvotes: 4

Guillaume Mercey
Guillaume Mercey

Reputation: 409

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

Upvotes: 16

Related Questions