Aggregates are also a regular source of confusion with NULLs, both in how aggregates handle NULLs on input, and when
aggregates return NULLs. Let’s look at the input case first:

CREATE TABLE aggtest (x INTEGER);
 
INSERT INTO aggtest VALUES (7), (8), (NULL);
 
SELECT COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x) FROM aggtest;
 count | count | sum | min | max |        avg
-------+-------+-----+-----+-----+--------------------
     3 |     2 |  15 |   7 |   8 | 7.5000000000000000

Continue Reading »