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?

Indeed. AND value_textarray <> '{}' took the cake. Thanks. — Tarps
– Tarps, Commented Apr 18, 2017 at 12:49
Alternatively: and coalesce(cardinality(value_textarray),0) = 0 — user330315
– user330315, Commented Apr 18, 2017 at 14:20
Be careful, if you filter empty arrays in where clause then you will miss that records — Balconsky
– Balconsky, Commented Apr 30 at 9:58

TD1 · Accepted Answer · 2022-01-19 16:04:34Z

17

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

edited Jan 19, 2022 at 16:04

TD1

3945 silver badges12 bronze badges

answered Feb 21, 2018 at 20:58

Guillaume Mercey

4194 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

richddr Over a year ago

This works and can probably be customized to allow concatenation null arrays.

iserdmi · Accepted Answer · 2023-05-09 12:22:15Z

4

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)

answered May 9, 2023 at 12:22

iserdmi

1332 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related