How to detect NULL rows in PostgreSQL sum()

Question

I want to aggregate the sum of a column, while tracking the presence of NULL values which signal an error condition. E.g., take the table numbers:

# select * from numbers;
  n   |  l  
------+-----
    1 | foo
    2 | foo
 NULL | bar
    4 | bar

Given a label l, I want to compute the sum of numbers n with that label, provided there are no NULL values. Ideally, for a label without any rows, the sum would be 0. So I'm looking for some query q such that q('foo') = 3, q('baz') = 0 and q('bar') somehow signals an error, e.g. by returning NULL.

I started with the sum() aggregate function, but that converts NULL rows to 0. One solution would be a variant that returns NULL provided there are any NULL values.

sum() gives

# select sum(n) from numbers where l = 'bar';
 sum 
-----
   4

but I'd rather have sumnull() with

# select sumnull(n) from numbers where l = 'bar';
 sumnull 
---------
  NULL

The best solution I've found so far is to also count non-NULL rows and compare to the total count:

# select sum(n), count(*), count(n) as notnull from numbers;
 sum | count | notnull 
-----+-------+---------
   7 |     4 |       3

Then if count is unequal to notnull, I know the result is not valid.

Answer 1

1) Use COALESCE

SELECT SUM(COALESCE(n,'NaN'::numeric)) FROM numbers;

If any row is NULL then result will be as 'NaN'

2) You will get NULL as result if any row has NULL value else result as number

SELECT 
    CASE WHEN (SELECT COUNT(*) FROM numbers WHERE n IS NULL) > 0 THEN NULL
        ELSE (SELECT SUM(COALESCE(n, 0)) FROM numbers)
END