On 10/09/2026 18:16, Tom Lane wrote:
Vik Fearing <[email protected]> writes:
It should be called PRODUCT and it should accept the syntax
PRODUCT(col, 1 ON EMPTY)
for when there are no non-nulls in the input.
Sigh. The committee really loves to invent randomly creative syntax,
don't they. At least this one won't force us to create any new
fully-reserved words.
Yes. I tried to fight back on it but I was overruled.
This will be required by the next edition of the SQL standard. The exact
value "1" is required by the standard, but I think we should allow any
a_expr there.
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.
SELECT string_agg(col, sep)
FROM (VALUES ('a', ','), ('b', ':'), ('c', 'd')) AS v (col, sep);
Result: a:bdc
Also, if PRODUCT can use this, SUM could use it even more,
and probably other aggregates too (but likely values would be
different from "1"). Did they generalize at all, or is this a
PRODUCT-specific wart?
It's for both PRODUCT and SUM (1 and 0 respectively) and not generalized
beyond that.
I tried to at least make it IDENTITY ON EMPTY but that wasn't even
understood!
--
Vik Fearing
