ON EMPTY clause for aggregate and window functions

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.

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.morland@gmail.com>
wrote:

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.

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

enterprisedb.com <https://www.enterprisedb.com&gt;

Hackers,

Following up on this thread (and the related discussion on the PRODUCT()
thread
/messages/by-id/CAM2+6=WG8gpOti+1-N_ra0mNcNrdhMjSJcDvcA7wzjLD-qKH3g@mail.gmail.com
):
I've reworked the ON EMPTY patch and would like feedback on the new
design before going further.

Thanks to Tom for pointing out the strict/non-strict distinction, and to Vik
for quoting the SQL committee's actual definition on the PRODUCT() thread:

PRODUCT(SQ VE, 1 ON EMPTY) => COALESCE(PRODUCT(SQ VE), 1)

i.e. the standard defines ON EMPTY, for the aggregates it actually
standardizes (SUM/PRODUCT), as nothing more than COALESCE over the whole
aggregate result. That prompted me to implement the general (PG extension)
version the same way.

What changed
---
ON EMPTY is now implemented as exactly:

agg(args, default ON EMPTY) == COALESCE(agg(args), default)

for both plain and window aggregates: compute the aggregate's ordinary
result exactly as without ON EMPTY, and substitute the default only if that
result is NULL. No new expression-evaluation step, no per-row bookkeeping,
and no restriction on partial/parallel aggregation.

The earlier design instead tracked, per group, whether any row was actually
fed to the transition function (to distinguish true emptiness from input
that merely computes to NULL). I dropped it: testing showed some built-in
aggregates use a differently-strict transition function for plain vs.
windowed use (e.g. sum(int4): int4_sum, non-strict, vs. int4_avg_accum,
strict), so the same clause over the same all-NULL data fired for a
windowed sum() but not a plain one. That's an implementation detail
leaking into user-visible behavior, not something worth preserving.

As a side benefit, the earlier restriction disabling partial/parallel
aggregation for ON EMPTY aggregates is also gone -- finalization always
happens centrally, so the COALESCE check works regardless of how the
transition state was combined.

The tradeoff, stated plainly
---
Because it's exactly COALESCE, ON EMPTY can't distinguish "zero rows" from
"rows were processed, but the result is legitimately NULL", for any
aggregate. For example, `stddev(x, -1 ON EMPTY)` over a single row returns
-1, even though that row was genuinely processed (stddev_samp of one value
is undefined and NULL by definition). Same story for percentile_cont over a
non-empty, all-NULL input.

I'd rather have a simple, uniform rule with a stated limitation than a
"smarter" rule that disagrees with itself depending on plan shape. Open to
recovering the stricter distinction for a subset of aggregates if there's
appetite for it, but I don't think it should be the default.

Patch and testing
---
Now a single patch (the earlier JIT-support patch is gone, since ON EMPTY no
longer needs a new expression step for the JIT compiler to support). Full
regression suite passes, including under a forced-JIT build.

Thanks,

On Sun, Jun 28, 2026 at 6:26 PM Jeevan Chalke <
jeevan.chalke@enterprisedb.com> wrote:

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.morland@gmail.com>
wrote:

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.

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

enterprisedb.com <https://www.enterprisedb.com&gt;

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

enterprisedb.com <https://www.enterprisedb.com&gt;

On 12/09/2026 15:18, Jeevan Chalke wrote:

ON EMPTY is now implemented as exactly:

agg(args, default ON EMPTY)  ==  COALESCE(agg(args), default)

I've taken a quick look at this, and I found a few bugs.

1) The first one is that the constant requirement only looks for actual
constants and not scoped constants.  For example:

CREATE TABLE cust (id INTEGER, name text, def_amount PRIMARY KEY (id));
CREATE TABLE ord (id INTEGER, custid INTEGER, amount INTEGER);
INSERT INTO cust SELECT g, 'c' || g, g FROM generate_series(1, 4) AS g (g);
INSERT INTO ord VALUES (1,1,100), (2,1,50), (3,3,7);

-- rejected: "ON EMPTY expression must be a constant value"
SELECT c.id,
       (SELECT SUM(o.amount, c.def_amount ON EMPTY)
        FROM ord AS o
        WHERE o.custid = c.id)
FROM cust AS c;

Here, the c.def_amount is constant for the subquery and should be
accepted.  The example is perhaps a bit contrived, but the logic is sound.

2) Another bug I found is this:

CREATE TABLE mm (a INTEGER);
INSERT INTO mm SELECT g FROM generate_series(1, 10_000) AS g (g);
ANALYZE mm;

SELECT COALESCE(MAX(a), -1) FROM mm WHERE a > 100_000;  --  -1
SELECT MAX(a, -1 ON EMPTY)  FROM mm WHERE a > 100_000;  --  -1

CREATE INDEX ON mm (a);

SELECT COALESCE(MAX(a), -1) FROM mm WHERE a > 100_000;  -- -1
SELECT MAX(a, -1 ON EMPTY)  FROM mm WHERE a > 100_000;  --  NULL

When MAX and MIN get optimized with an index, the ON EMPTY seems to be
dropped.

3) The set quantifier is not recognized.

SELECT SUM(ALL      a, 0 ON EMPTY) FROM t;
SELECT SUM(DISTINCT a, 0 ON EMPTY) FROM t;

Neither of those parse.

I will keep reviewing this feature.

--

Vik Fearing

On Sat, Sep 12, 2026 at 9:19 AM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

ON EMPTY is now implemented as exactly:

agg(args, default ON EMPTY) == COALESCE(agg(args), default)

for both plain and window aggregates: compute the aggregate's ordinary
result exactly as without ON EMPTY, and substitute the default only if that
result is NULL. No new expression-evaluation step, no per-row bookkeeping,
and no restriction on partial/parallel aggregation.

This seems pretty useless -- the new syntax is more work for us to
maintain, and doesn't really add any value over just using COALESCE.

To be clear, I expect to lose this argument on the grounds that
apparently this syntax is in the spec and therefore we ought to
support it. But I don't understand why the spec -- or we -- should
spend time inventing new ways to spell existing behaviors. That just
seems confusing (and this particular choice of syntax seems
extra-confusing).

--
Robert Haas

Robert Haas <robertmhaas@gmail.com> writes:

This seems pretty useless -- the new syntax is more work for us to
maintain, and doesn't really add any value over just using COALESCE.

It could add value, in scenarios where substitute-for-NULL doesn't
give quite the behavior you want. But that's not so for the two
aggregates the spec has bothered to define this for, and I'm having
a hard time coming up with an aggregate for which it would be so.

If somebody actually did have an aggregate that could return null for
more than zero input rows, they could always do

CASE WHEN count(x) > 0 THEN frobnitz(x) ELSE value_for_zero_rows END

which has the extra benefit that you can choose "count(x)" or
"count(*)" depending on your desires for what to do with null
inputs.

To be clear, I expect to lose this argument on the grounds that
apparently this syntax is in the spec and therefore we ought to
support it. But I don't understand why the spec -- or we -- should
spend time inventing new ways to spell existing behaviors. That just
seems confusing (and this particular choice of syntax seems
extra-confusing).

Yes, this choice of syntax sucks pretty badly. In the COALESCE
spelling, it's crystal clear that the substitute value is not an
aggregate argument and so is evaluated at most once (per group);
there's no need for gamesmanship around restricting it to be a
constant.

I'd be totally fine with rejecting this as a frammish we do
not care to support.

regards, tom lane

On 17/09/2026 15:31, Tom Lane wrote:

Yes, this choice of syntax sucks pretty badly.

I agree. The next edition isn't out yet, if we would like to suggest
something better.

I'd be totally fine with rejecting this as a frammish we do
not care to support.

I am not the most unbiased person, but I would like us to implement it. 
If for nothing else, then for helping people convert from database
implementations that do have it.

--

Vik Fearing

Vik Fearing <vik@postgresfriends.org> writes:

On 17/09/2026 15:31, Tom Lane wrote:

I'd be totally fine with rejecting this as a frammish we do
not care to support.

I am not the most unbiased person, but I would like us to implement it.
If for nothing else, then for helping people convert from database
implementations that do have it.

It's not zero cost. Robert already mentioned the
development/maintenance effort involved, and it also bloats the Bison
grammar rules, creating some incremental penalty on parsing speed.
Admittedly these costs aren't large, but neither is the benefit
of supporting it.

regards, tom lane