Question 114318 in PostgreSQL Question posted in
The official documentation can be found here.

How does postgresql cast float to numeric?

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?

Tags:

2

Answers


  1. 0

    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);
}
    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search