Make PostgreSQL aggregate function sum() return NULL if at least one of the addendum is NULL-CodePudding

In PostgreSQL aggregate function sum() returns null if in the group there is only one member with value null: SELECT sum(null::integer); gives null. If in the group there is more than one addendum and at least one of them is not null, then remaining addendums with value null will be converted as 0 and then summed all addendums. so sum of group (null, null, 3, 0, 1) returns 4, not null as SELECT null::integer null::integer 3 0 1; does. Is there a way to make the function sum return null when at least one addendum in group is null? It would be coherent with operator behavior and helpful in my case.

CodePudding user response：

You can create your own aggregate with the expected behavior :

CREATE OR REPLACE FUNCTION sum_null(x anyelement, y anyelement)
RETURNS anyelement LANGUAGE sql AS $$
SELECT x   y ; $$ ;

CREATE OR REPLACE AGGREGATE sum_null(x anyelement)
( SFUNC = sum_null, STYPE = anyelement, INITCOND = 0) ;

See the test result in dbfiddle