BUG #15925: Loss of precision converting money to numeric

PG Bug reporting form <noreply@postgresql.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

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.

Thanks Tom,

the response is illuminating. And a same-day patch is legendary.

Thank you for your work.
Slawo