CODEWITHSUNDEEP
postgresql
user4597030
user4597030

Reputation:

Let array_agg() return no rows when there are no input rows

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.

Upvotes: 0

Views: 152

Answers (1)

Marth
Marth

Reputation: 24802

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)

Upvotes: 1

Related Questions