Re: BUG #15925: Loss of precision converting money to numeric

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> During my testing I found unexpected results for the min and max value of
> the money type.
> select '-92233720368547758.08'::money,
> '-92233720368547758.08'::money::numeric(30,2);
> money |numeric |
> ---------------------------|---------------------|
> -$92,233,720,368,547,758.08|-92233720368547758.00|
> Note that the cent value is gone after converting to numeric.
> Same issue for the max value:
> money |numeric |
> --------------------------|--------------------|
> $92,233,720,368,547,758.07|92233720368547758.00|

Hmm, yeah, anything approaching INT64_MAX has a problem.
The issue is that cash_numeric() does the equivalent of

SELECT 9223372036854775807::numeric / 100::numeric;

and if you try that by hand you indeed get

92233720368547758

because select_div_scale() has decided that it need not produce
any fractional digits. We can force its hand by making the input
have the required number of fractional digits *before* dividing,
which is a bit weird on its face but gets the job done, per the
comment therein:

* 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 16, whence the problem for a 17-digit result.
Maybe we should consider raising that, but I'm hesitant to consider such
a far-reaching change just to make cash_numeric happy.)

I intend to apply the attached patch.

regards, tom lane