How does postgresql cast float to numeric?

Question

I was wondering how Postgresql converts floating point (float4) values to NUMERIC.

I chose 0.1 as a testing value. This value is not accurately representable in base2, see https://float.exposed/0x3dcccccd for a visualization. So the stored value 0x3dcccccd in hex for a float4 is actually not 0.1 but 0.100000001490116119385.

However, I do not understand the output of the following commands:

mydb=# SELECT '0.100000001490116119385'::float4::numeric(50,50);
                       numeric                        
------------------------------------------------------
 0.10000000000000000000000000000000000000000000000000
(1 row)

mydb=# SELECT '0.1'::float4::numeric(50,50);
                       numeric                        
------------------------------------------------------
 0.10000000000000000000000000000000000000000000000000

mydb=# SELECT '0.10000000000000000000000000000000001'::float4::numeric(50,50);
                       numeric                        
------------------------------------------------------
 0.10000000000000000000000000000000000000000000000000

Why (and how) do I get 0.1 as a result in all cases? Both, 0.1 and 0.10000000000000000000000000000000001 cannot be accurately stored in a float4. The value that can be stored is 0.100000001490116119385 which is also the closest float4 value in both cases, but that's not what I get when casting to numeric. Why?

jian · Accepted Answer · 2022-10-07 08:47:24Z

Further explanation of Frank Heikens's answer

source code idea is get the float4 input. convert to char string, then convert to numeric.

Key function is snprintf(buf, sizeof(buf), "%.*g", FLT_DIG, val);

FLT_DIG is equal to 6.
https://pubs.opengroup.org/onlinepubs/7908799/xsh/fprintf.html

An optional precision that gives the minimum number of digits to appear for the d, i, o, u, x and X conversions; the number of digits to appear after the radix character for the e, E and f conversions; the maximum number of significant digits for the g and G conversions; or the maximum number of bytes to be printed from a string in s and S conversions. The precision takes the form of a period (.) followed either by an asterisk (*), described below, or an optional decimal digit string, where a null digit string is treated as 0. If a precision appears with any other conversion character, the behaviour is undefined.

float convert to text then to numeric processs: the text after decimal delimiter can only have 6 digits precision!

snprintf example: https://legacy.cplusplus.com/reference/cstdio/snprintf/
further post: Avoid trailing zeroes in printf()

Collectives™ on Stack Overflow

How does postgresql cast float to numeric?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related