Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

SELECT f8                  AS float8
     , f8::bigint          AS to_bigint
     , f8::numeric         AS to_numeric
     , f8::numeric(20,0)   AS to_numeric20
FROM  (
   VALUES
     ('8217316934885843456'::float8)
   , ('8217316934885843457')
   , ('8217316934885844479')
   , ('8217316934885844480')   
   , ('8217316934885845503')
   , ('8217316934885845584')   
   ) t(f8);


float8
to_bigint
to_numeric
to_numeric20


8.217316934885843e+18
8217316934885842944
8217316934885840000
8217316934885840000

8.217316934885844e+18
8217316934885843968
8217316934885840000
8217316934885840000

8.217316934885844e+18
8217316934885843968
8217316934885840000
8217316934885840000

8.217316934885845e+18
8217316934885844992
8217316934885840000
8217316934885840000

8.217316934885845e+18
8217316934885844992
8217316934885840000
8217316934885840000

8.217316934885846e+18
8217316934885846016
8217316934885850000
8217316934885850000


-- comparing after cast to numeric is less precise!
SELECT '8217316934885843456'::float8          = '8217316934885843457'::float8
     , '8217316934885843456'::float8::bigint  = '8217316934885843457'::float8::bigint
     , '8217316934885843456'::float8::numeric = '8217316934885843457'::float8::numeric


?column?
?column?
?column?


f
f
t


-- cast to numeric is lossy, while cast to bigint is not.
SELECT '8217316934885843456'::float8 = '8217316934885843456'::float8::bigint::float8
     , '8217316934885843456'::float8 = '8217316934885843456'::float8::numeric::float8


?column?
?column?


t
f

float8	to_bigint	to_numeric	to_numeric20
8.217316934885843e+18	8217316934885842944	8217316934885840000	8217316934885840000
8.217316934885844e+18	8217316934885843968	8217316934885840000	8217316934885840000
8.217316934885844e+18	8217316934885843968	8217316934885840000	8217316934885840000
8.217316934885845e+18	8217316934885844992	8217316934885840000	8217316934885840000
8.217316934885845e+18	8217316934885844992	8217316934885840000	8217316934885840000
8.217316934885846e+18	8217316934885846016	8217316934885850000	8217316934885850000