skip to Main Content

I understand how double precision works (exponent, mantissa, precision depending of the value, not exactly accurate numbers…) and I’m currently writing a blog post on this subject.
But when using it in PostgreSQL I found that PostgreSQL round these values.

The config parameter extra_float_digits=1 (or more) allows PostgreSQL to display every digits but this is not the case.

For instance : 1.1 can not be exactly stored in double precision, it is approximated as 1.100000000000000088817841970012523233890533447265625 because this is the closest value that can be stored. The previous/next step is far from 2,2204460492 × 10^−16
Why this stored number can not be visualised ?

I think that the code is rounding the number to 10^-15 because this is the max precision you can have for this order of magnitude but I can not confirm this.

I have three questions :

  1. Does PostgreSQL round the value ?
  2. Is it possible to have the exact value stored ?
  3. Is it already the case but I make some mistakes (I’m using PSQL) ?

I set :

SET extra_float_digits=3;

I try

SELECT 1.1::float8;
 float8
--------
    1.1

The result is 1.1 which is not the exact stored value.
The same for :

SELECT 1.1::float8::varchar;
 varchar
---------
 1.1

If I try with the exact value :

SELECT 1.100000000000000088817841970012523233890533447265625::float8;
 float8
--------
    1.1

2

Answers


  1. Chosen as BEST ANSWER

    The answer is : YES PostgreSQL round values.

    Thanks to @vérace I can found it into the source code of PostgreSQL where the d2d function is used to output the decimal form of the value.

    This is quite complicated but to simplify, a validity interval is calculated depending on the exponent and the mantissa. Then the decimal value is rounding in consistency to the validity interval.
    The final result is rounded to about (really about) the value multiply by 10^-16.

    This answer will be marked as "accepted" when I will be granted to ;-)


  2. SELECT round( CAST(float8 '1.100000' as numeric), 2);

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search