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?

score 1 · Answer 1 · answered Oct 07 '22 at 06:55

From the source code:

Datum
float4_numeric(PG_FUNCTION_ARGS)
{
    float4      val = PG_GETARG_FLOAT4(0);
    Numeric     res;
    NumericVar  result;
    char        buf[FLT_DIG + 100];

    if (isnan(val))
        PG_RETURN_NUMERIC(make_result(&const_nan));

    if (isinf(val))
    {
        if (val < 0)
            PG_RETURN_NUMERIC(make_result(&const_ninf));
        else
            PG_RETURN_NUMERIC(make_result(&const_pinf));
    }

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

    init_var(&result);

    /* Assume we need not worry about leading/trailing spaces */
    (void) set_var_from_str(buf, buf, &result);

    res = make_result(&result);

    free_var(&result);

    PG_RETURN_NUMERIC(res);
}

Not good enough. You should at least explain `FLT_DIG`. – Laurenz Albe Oct 07 '22 at 09:52 — Laurenz Albe, Oct 07 '22 at 09:52

score 1 · Answer 2 · answered Oct 07 '22 at 08:47

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()

How does postgresql cast float to numeric?

2 Answers2