Re: Question about the TODO, numerics, and division - Mailing list pgsql-hackers

Chris Travers <chris@verkiel.metatrontech.com> writes:
> I have been looking at the TODO and have found something that I find 
> sort of odd and we should probably reconsider:

> One of the items under data types is:

>     * Add NUMERIC division operator that doesn't round?

>       Currently NUMERIC _rounds_ the result to the specified precision.
>       This means division can return a result that multiplied by the
>       divisor is greater than the dividend, e.g. this returns a value > 10:
>       SELECT (10::numeric(2,0) / 6::numeric(2,0))::numeric(2,0) * 6;

I agree that the TODO item is pretty bogus as worded.  A closer look
at what's going on is:

regression=# SELECT (10::numeric(2,0) / 6::numeric(2,0)) ;     ?column?
--------------------1.6666666666666667
(1 row)

and of course if you multiply that by 6 you get

regression=# SELECT (10::numeric(2,0) / 6::numeric(2,0)) * 6;     ?column?
---------------------10.0000000000000002
(1 row)

However this seems basically insoluble.  The TODO item seems to imagine
that it would be better if the division returned 1.6666666666666666,
but AFAICS that answer is actually *less* accurate:

regression=# select 1.6666666666666666 * 6;     ?column?
--------------------9.9999999999999996
(1 row)

regression=#

The only way to make it more accurate is to return more decimal places,
but you'll never get an exact result, because this is a nonterminating
fraction.

There may be a use for a division operator that rounds the last returned
digit towards minus infinity instead of to nearest, but the TODO entry
is utterly unconvincing as an argument for that.  Does anyone recall
what the original argument was for it?  Perhaps the TODO entry is
just mis-summarizing the discussion.

A separate question is whether the division operator chooses a good
default for the number of digits to return.  You can make it compute
more digits by increasing the scale values of the inputs:

regression=# SELECT (10::numeric(32,30) / 6::numeric(2,0)) ;            ?column?
----------------------------------1.666666666666666666666666666667
(1 row)

but I wouldn't want to defend the details of the rule about how many
fractional digits out given so many fractional digits in.
        regards, tom lane