Why does the cast from double precision to numeric round to 15 significant digits?

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)

db<>fiddle here

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

Bigint will be more precise anyway (19 digits at most).
The observed behavior of conversion between float8 and numeric makes sense if we assume that extra_float_digits value is set to 0;

SET extra_float_digits = 0;
SELECT '8217316934885843456'::numeric::text
UNION ALL
SELECT '8217316934885843456'::float8::text
UNION ALL
SELECT '8217316934885843456'::float8::numeric::text

result:

8217316934885843456
8.21731693488584e+18
8217316934885840000

If you increase extra_float_digits (the default value is 1, but you can increase it up to 3), the precision of conversion to float8 changes (but not past 1 digit), but the precision of conversion from float8 to numeric doesn't change. It likely means somewhere in Postgres code it uses "old" precision logic for this conversion. It could be a bug or an oversight, but there could be a genuine reason for that (which I don't know unfortunately).

For compatibility with output generated by older versions of PostgreSQL, and to allow the output precision to be reduced, the extra_float_digits parameter can be used to select rounded decimal output instead. Setting a value of 0 restores the previous default of rounding the value to 6 (for float4) or 15 (for float8) significant decimal digits.

StackExchange Database Administrators Q#300490, answer score: 2