Sample Header Ad - 728x90

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