Re: ON EMPTY clause for aggregate and window functions

Hello Isaac,

Good points on both counts.

Regarding INITCOND, you are correct that it only sets the initial state.
I tried exploiting it in my very first attempt too. However, if the
aggregate isn't invoked at all (zero input rows), INITCOND is never
used to determine the final result, which is why we need this mechanism
to return something at finalization when the input set was empty.
The majority of the code changes here are to determine whether we've
received any input or not.

I'm open to renaming 'default_value' if a better term comes up.
'Value_at_empty_input' is certainly more precise than 'default',
but it's long. I'll keep an eye out for other suggestions; I'm not
attached to the current name and will be happy to rename it once we
settle on one.

Thanks for reviewing.

On Fri, Jun 26, 2026 at 6:51 PM Isaac Morland <isaac(dot)morland(at)gmail(dot)com>
wrote:

> 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.
>

--
*Jeevan Chalke*
*Senior Principal Engineer, Engineering Manager*
*Product Development*

enterprisedb.com <https://www.enterprisedb.com>