1

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:

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

=> SELECT array_agg(a) IS NULL FROM (SELECT 1 a) t WHERE a > 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?

2
  • 1
    @ypercubeᵀᴹ I just tried. SUM(a) returns NULL as well. I would think it should return 0. – tinlyx Jan 31 '20 at 19:28
  • 1
    The documentation spells this out concisely: “It should be noted that except for count, these functions return a null value when no rows are selected. In particular, sum of no rows returns null, not zero as one might expect, and array_agg returns null rather than an empty array when there are no input rows. The coalesce function can be used to substitute zero or an empty array for null when necessary.” postgresql.org/docs/12/functions-aggregate.html – fds Jan 31 '20 at 20:33
1

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.

1

There are continuous debates within Postgresql about when to return null and when to not return NULL from aggregate function or with any function within prostgresql and this debate rages on in other DBs as well..

In your test case Count(*) the where class returns a empty set aka a NULL so count would return 1 as it had 1 empty/NULL set to count

Array_AGG() return a 1 dimensional array with a NULL value it; from the empty set aka NULL value and put it into the array.

Array_AGG() as stated in the documentation takes in any argument including nulls and returns that value, The test case creates a NULL condition return set so it would return NULL value.

The justification is Aggregate_Array takes in everything it uses no filtering

With Sum() NULLs are unknown, not a value, not a number, empty set, Sum can not determine a value when faced with a undefined value, Even when returning Zero results in assigning a value to unknown condition.

Consider this odd thing

select  Sum(dd), Count(*), Count(dd) from (
Select NULL::integer as dd
union
Select NULL::integer as dd
union 
Select NULL::integer as dd
    ) ff

value return  is 
NULL, 1, 0

Why Sum actual filters out NULLs when it does it math if not NULL + anything should result in NULL. Take note of the different results that COUNT returns

Count(*) is special condition where is counts everything regardless of its type. change it to count(dd) a column it does something different

select  Sum(dd), Count(*), Count(dd) from (
Select NULL::integer as dd
union
Select 1 as dd
union 
Select NULL::integer as dd
    ) ff

value returned 
1, 2, 1

In this case Count(*) had two conditions to count NULL and 1 so it outputted 2, yet count(dd) return just 1 as it filtered out the NULL

COUNT(*) has some quirky behavior as it takes in anything and everything as do other functions that can take in any datatype

In short watch out for functions that take in any datatype because its treatment of NULL may not be what you expect such as Count(*) as it counts everything..

1
  • 1
    It is the SQL standard that requires that NULL arguments are ignored in aggregates (which the oddball array_agg does not respect). – Laurenz Albe Feb 2 '20 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.