On Fri, Sep 11, 2026 at 5:16 AM Tom Lane <[email protected]> wrote:
> Vik Fearing <[email protected]> writes: > > On 10/09/2026 18:16, Tom Lane wrote: > >> I think that this may be trickier than it looks. You'd really want > >> that to act like a "direct" argument, ie evaluate once not once per > >> row. > > > That's fair, but it isn't what happens for string_agg, for example, > > which can have a different separator per value. > > Sure, but that's not the same thing. You can do something credible > with a separator-per-value in string_agg, but it's nonsense to suppose > that ON EMPTY is a per-row value. If it were per-row, which value > would you use? I assume it applies even if there are zero input rows, > not only if there are some inputs but they happen to all be null. > In my currently proposed patch ( https://www.postgresql.org/message-id/CAM2+6=VS=fSKxfimW6Th9iu_xjbxOEAKg4eYwaa=smg3x8p...@mail.gmail.com), the ON EMPTY value is strictly returned only when there are zero input rows. Rows containing NULL are treated as valid rows and do not trigger the ON EMPTY clause. > > The committee is evidently choosing to sidestep the > how-many-evaluations question by insisting on a constant value, > which may well be sufficient for all real-world cases. If we want > it to be "any a_expr" though, we have to think about that. > > > It's for both PRODUCT and SUM (1 and 0 respectively) and not generalized > > beyond that. > > OK, at least the SUM case occurred to them ;-). But I think for > our purposes we definitely want to allow it for any aggregate. > Yes, the proposed patch supports this for all aggregate functions and is not restricted to specific ones at the moment. Additionally, while the grammar accepts an a_expr, the backend code includes checks to ensure the provided expression is a constant value that is type-coercible to the aggregate's result type. I would be very happy to receive any feedback or comments on that thread as well. Thanks, > > regards, tom lane > -- *Jeevan Chalke* *Senior Principal Engineer, Engineering Manager* *Product Development* enterprisedb.com <https://www.enterprisedb.com>
