> > Is there any reason for not use these functions on SUM() and AVG()
> on
> > official release ?
>
> It sounds like a good idea. The only hesitation I have at the moment
> is
> that not all platforms have int8 support, and I'm not certain which
> these are. Also, accumulating int4 into int8 is probably pretty slow
> since on 32-bit machines the "long long" is usually done in a s/w
> library, not in machine code.
>
> float8 might be a better choice for accumulating AVG(), but I'm
> worried
> about incorrect results with large tables (> 1M entries) which have
> pathological distributions of numbers (e.g. 1M entries with MAXINT and
> 1M entries with zero). int4 gives ~9.2 decimal places, float8 gives
> ~15
> decimal places, so there is only about ~6 decimal places of headroom.
>
> Of course, why am I worried? That is much better than what we have
> currently. And someone reported that at least one commercial system
> (Sybase?) returns float8 for avg() (and sum()?) as I recall.
>
> So, your suggestion is that for AVG() at least we return something
> other
> than the input type; how about returning float8 for any input type?
> Don't know if SUM() could/should behave similarly...
>
> - Tom
>
I think the issue could be address if a float8 sum would be affected by
an ORDER BY. If so just make a not in the DOCS and FAQ about
significant digits in SUM and AVG. And maybe an example to get the most
exact SUM and AVG from a table. If SUM and AVG aren't affected by an
ORDER BY I'd say stick with the highest range integer type implemented
by the system (in hardware if possible).
Just my $0.02,
-DEJ