Re: ON EMPTY clause for aggregate and window functions - Mailing list pgsql-hackers

On Fri, 26 Jun 2026 at 05:12, Jeevan Chalke <jeevan.chalke@enterprisedb.com> wrote:

Hello Hackers,

Here is a patch set adding an optional ON EMPTY clause to aggregate (and
aggregate-as-window-function) calls.  It supplies a value to return when the
aggregate processes no input rows at all:

    agg_function(args, default_value ON EMPTY)

For example:

    SELECT sum(i, -1 ON EMPTY)
    FROM generate_series(1,10) AS s(i) WHERE i > 100;
     sum
    -----
      -1
    (1 row)

ON EMPTY is triggered only by an empty input set, not by NULL inputs that are
ignored during aggregation.  A FILTER that removes all rows makes the input
set empty, so the default applies in that case too.  Because a grouped query
never produces empty groups, ON EMPTY takes effect for an ungrouped aggregate
over zero rows, or for a group whose rows are all removed by FILTER.  For an
aggregate used as a window function, the default is returned for any row whose
frame contains no rows.  It also works with an ordered-set aggregate, with the
default written before WITHIN GROUP:

Is there any chance of storing a default default_value with the aggregate? I ask because for most aggregate functions there is a specific value for each function which is almost always what will be wanted, e.g., 0 for sum, -Infinity for max, +Infinity for min, 1 for multiplication (if the other proposal for a multiplication aggregate is accepted), and so on, generally characterizable as the identity element for the function in question. Only in rare cases would one actually want to override the identity and use some other specified value. 

I was going to suggest there would need to be an additional clause for CREATE AGGREGATE, but I see there is already an INITCOND parameter which in principle should already be doing the job (except that as I understand it the aggregate isn't invoked at all for empty input?).

Also I'm not entirely happy with the name "default_value". It's not really a default, just the value of the aggregate at empty input. Unfortunately, I don't have a better suggestion.