Re: ON EMPTY clause for aggregate and window functions

Hi Robert, Tom, Vik,

On Thu, Sep 17, 2026 at 9:25 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Vik Fearing <vik(at)postgresfriends(dot)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.
>

Following up on the "is this worth it" question. I agree with Robert and
Tom: as I built it, ON EMPTY is exactly COALESCE(agg(...), default),
always, for any aggregate. I could not find a case where it gives a
different answer than COALESCE. So the general version does not add
real value over COALESCE, and I will not argue that point further.

But I think there is a much smaller version worth considering, and I
built it to check, instead of just guessing. Patch attached.

It only accepts ON EMPTY on sum() and product(), the two aggregates the
standard actually defines this for. At parse time, once we know the
call is sum() or product(), it is just rewritten to
COALESCE(sum(expr), default). Nothing else changes:

- no new field on Aggref or WindowFunc
- no executor changes
- no CATALOG_VERSION_NO bump
- no special case in the MIN/MAX index-scan code
- DISTINCT and ALL work with no extra code, since dedup happens
before the rewrite even runs

Total diff is about 110 lines (grammar, one FuncCall field, parser),
under 150 with tests. I ran the full regression suite and some manual
dump/restore checks: it all works, though view/rule dumps show the
COALESCE rewrite, not the original ON EMPTY spelling, since the
rewrite happens before anything gets stored. Text-body SQL functions
still show the original spelling, since those store raw text.

Vik, I think this still gives you what you wanted for migration: the
two aggregates the standard defines this for now match it exactly,
just spelled the standard way.

Thoughts?

Thanks,

>
> regards, tom lane
>

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

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