Why does the cast from double precision to numeric round to 15 significant digits?
9
votes
1
answer
5348
views
The cast from
double precision
(float8
) to numeric
rounds to 15 significant decimal digits, thereby losing information. Clearly, more precision is possible. The cast to bigint
(for values within its range) preserves more precision:
SELECT f8 AS float8
, f8::bigint AS to_bigint
, f8::numeric AS to_numeric
FROM (
VALUES
('8217316934885843456'::float8)
, ('8217316934885843457')
, ('8217316934885844479')
, ('8217316934885844480')
, ('8217316934885845503')
, ('8217316934885845584')
) t(f8);
float8 | to_bigint | to_numeric
-----------------------+---------------------+---------------------
8.217316934885843e+18 | 8217316934885842944 | 8217316934885840000
8.217316934885844e+18 | 8217316934885843968 | 8217316934885840000
8.217316934885844e+18 | 8217316934885843968 | 8217316934885840000
8.217316934885845e+18 | 8217316934885844992 | 8217316934885840000
8.217316934885845e+18 | 8217316934885844992 | 8217316934885840000
8.217316934885846e+18 | 8217316934885846016 | 8217316934885850000
(6 rows)
*dbfiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=89538ad467b701f391aae67a8bff3ff1)*
I picked values at boundaries where float8
flips a bit - at least in my local installation (Postgres 13, Ubuntu, Intel CPU), and on dbfiddle, and in a hosted DB on AWS, too).
Some relevant quotes from the current manual (Postgres 14):
In the overview of numeric types
> double precision
... **15 decimal digits** precision
In the chapter for floating point numbers :
> The double precision
type has a range of around 1E-307
to 1E+308
with a **precision of at least 15 digits**.
And:
> By default, floating point values are output in text form in their shortest precise decimal representation; the decimal value produced is closer to the true stored binary value than to any other value representable in the same binary precision. (However, the output value is currently never *exactly* midway between two representable values, in order to avoid a widespread bug where input routines do not properly respect the round-to-nearest-even rule.) This value will use **at most 17 significant decimal digits** for float8
values, and at most 9 digits for float4
values.
Bold emphasis mine.
So why not preserve *at most 17 significant decimal digits* in the cast to numeric
? The cast to bigint
does better!
This has counter-intuitive (at least for me) effects. A cast to numeric
loses precision, while a cast to bigint
does not.
test=> SELECT '8217316934885843456'::float8 = '8217316934885843456'::float8::bigint::float8
test-> , '8217316934885843456'::float8 = '8217316934885843456'::float8::numeric::float8;
?column? | ?column?
----------+----------
t | f
It's a notoriously tricky matter. So maybe there are good reasons for capping at 15 digits that I fail to see?
Or could Postgres do better?
Asked by Erwin Brandstetter
(185747 rep)
Oct 2, 2021, 03:39 AM
Last activity: Sep 13, 2022, 05:53 AM
Last activity: Sep 13, 2022, 05:53 AM