Re: BUG #15812: Select statement of a very big number, with a division operator seems to round up.

>>>>> "PG" == PG Bug reporting form <noreply(at)postgresql(dot)org> writes:

PG> I have a need to divide a big number numeric(20) by 10000000000 to
PG> feed it into a floor function. The division operation rounds up the
PG> number which causes problems. I need the division to just divide
PG> the number without rounding up or down. For my purposes
PG> 3691635539999999999/10000000000 should return 369163553.9999999999
PG> not 369163554. This happens if the data is retrieved from a column.

There seems to have been a bit of confusion in the prior responses here.

The first thing to understand is that numeric/numeric _must_ in general
round the result to _some_ precision, since otherwise the output of
1.0/3.0 would be infinitely long. (Whereas numeric addition,
subtraction, and multiplication can always give exact results.)

The question is how many digits of precision the division function
chooses. The documentation seems to be silent on this; the code says:

* The result scale of a division isn't specified in any SQL standard. For
* PostgreSQL we select a result scale that will give at least
* NUMERIC_MIN_SIG_DIGITS significant digits, so that numeric gives a
* result no less accurate than float8; but use a scale not less than
* either input's display scale.

NUMERIC_MIN_SIG_DIGITS is defined to be 16.

So here you're dividing 3691635539999999999::numeric, which has a
display scale of 0, by 10000000000::numeric, which also has a display
scale of 0. 369163553.9999999999 is 19 significant digits; the chosen
result scale is 8 because that gives at least the minimum 16 significant
digits.

(Now, it can be argued that PG's choice of result scale for division is
more surprising than it could be. But coming up with a non-surprising
rule is not easy.)

When a numeric value comes from a table column that has a declared
scale, like numeric(20,0), then the value always has the specified
scale. You can force the scale to a specific value using round(x,n)
(usually more convenient than adding a ::numeric(blah,n) cast).

For your example, it might be more convenient to do:

select BIG_NUM*(1.0/10000000000) ...

which will always give exact results when the divisor is a power of 10.

--
Andrew (irc:RhodiumToad)