Inconsistent behavior of aggregate functions about whether to return NULL on empty input? On an empty input table, PostgreSQL (12.x) is returning NULL on some aggregate functions like array_agg() but normal values for others like count(), as shown below:

=&gt; SELECT count(*) IS NULL FROM (SELECT 1 a) e WHERE a &gt; 1;
 ?column? 
----------
 f
(1 row)

=&gt; SELECT array_agg(a) IS NULL FROM (SELECT 1 a) t WHERE a &gt; 1;
 ?column? 
----------
 t
(1 row)


Why does not array_agg() return a normal value (i.e., an empty array) in case of empty input?

This is somewhat disturbing as it's difficult to remember which ones can return NULL.

Related to this, what are the aggregates that return NULL on empties?
I can't speak from any experience but SQL Server, but I'll assume that this applies to most/all SQL language implementations:

COUNT(*) is counting rows. If there are zero rows to count, zero is the response.

Most other aggregate functions depend upon values contained in fields (MIN, MAX, SUM, etc). If there are no values to aggregate (either because no records are in the dataset or only NULL values in the field(s) involved), NULL is the response.