Re: ON EMPTY clause for aggregate and window functions

On Fri, 26 Jun 2026 at 05:12, Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)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.