Re: 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