Re: Add PRODUCT() aggregate function

On Tue, Jun 23, 2026 at 2:13 PM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
wrote:

> On Tue, 23 Jun 2026 at 09:37, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
> wrote:
> >
> > On Tue, 23 Jun 2026 at 08:49, Jeevan Chalke
> > <jeevan(dot)chalke(at)enterprisedb(dot)com> wrote:
> > >
> > > PRODUCT() returns the product of all non-null input values. It is
> defined for
> > > int2, int4, int8, float4, float8 and numeric input, and always returns
> numeric.
> >
> > I don't think that you need to define it for all those types. I
> > suspect that you could just define it for numeric and float8, and let
> > implicit casting do the rest.
>

Thank you, Dean, for looking at this.

I appreciate the suggestion, but I don't think the implicit-casting
approach
works well here, for the following reasons:

1. The integer types (int2/int4/int8) have implicit casts to both float8
and
numeric. Since float8 is the preferred type in the numeric type category,
the function resolution machinery would select the product(float8) variant.
That has two consequences: an extra cast function has to be executed per
row,
and, more importantly, integer inputs would be accumulated as float8.
For a large product that yields a lossy result in exponent form, whereas
accumulating in numeric gives an exact answer.

2. This approach is also consistent with the existing aggregates —
sum(), avg(), min()/max(), etc. all define per-type variants rather than
relying on implicit input casting. The patch follows that established
pattern rather than introducing a new one.

3. There is also a small performance penalty to the casting approach:
the per-row execution of the cast function itself, in addition to the
resolution issue noted in (1).

I ran a quick test to demonstrate point (1). For large products, the float8
implementation returns a lossy, exponential result like 9.9999970000003e+20,

whereas numeric returns the exact value of 999999700000029999999.

I would be happy to share the test script if it's helpful.

>
> ... and perhaps make the float8 version return float8.
>

Yes, I already noted this under "Open questions" in my first email. We can
certainly make the float4 and float8 variants return float8 instead of
numeric. Let's see what others think before making that adjustment.

Thanks

>
> Regards,
> Dean
>

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

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