From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | slawomir(dot)chodnicki(at)gmail(dot)com |
Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #15925: Loss of precision converting money to numeric |
Date: | 2019-07-26 15:17:34 |
Message-ID: | 12463.1564154254@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
Attachment | Content-Type | Size |
---|---|---|
fix-cash_numeric-for-large-values.patch | text/x-diff | 4.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Slawomir Chodnicki | 2019-07-26 16:15:00 | Re: BUG #15925: Loss of precision converting money to numeric |
Previous Message | Fahar Abbas | 2019-07-26 14:20:15 | Re: Error CREATE EXTENSION plpythonu |