When there are no rows, the function array_agg() returns null. How can I alter this so that no rows are returned? For example, take the following simple query:
select array_agg(country)
from country
where false
This returns one row with value null. I would like to have no rows returned.
Use a HAVING clause to remove empty arrays:
[local] #= select array_agg(1) from a where false;
┌───────────┐
│ array_agg │
├───────────┤
│ (null) │
└───────────┘
(1 row)
[local] #= select array_agg(1) from a where false having array_agg(1) <> '{}';
┌───────────┐
│ array_agg │
├───────────┤
└───────────┘
(0 rows)
