Re: PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue

Andreas Joseph Krogh <andreas@visena.com> writes:
>  How can this be sane? 

If you're expecting exact results from float calculations, you need
a refresher course in what floating-point arithmetic is all about.

Standard IEEE float4 representation can handle somewhere between
six and seven decimal digits of accuracy.  It's common to round
off the display to six digits to make sure that you don't see
any bogus digits.  float4_numeric() does that:

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

which explains the results David gets from testing direct
float4-to-numeric coercions.  However, that's not the same
code path used for textual display of float4.  float4out()
can be coerced into showing possibly-imprecise digits:

regression=# select '1234567'::float4;
   float4    
-------------
 1.23457e+06
(1 row)

regression=# show extra_float_digits; 
 extra_float_digits 
--------------------
 0
(1 row)

regression=# set extra_float_digits = 1;
SET
regression=# select '1234567'::float4;
 float4  
---------
 1234567
(1 row)

(Also note that v12 and later behave differently still, since they
use a new algorithm for converting floats to decimal.)

I'm not sure though why a JDBC driver change, without a server change,
would affect anything here.  Maybe the driver is/was changing
extra_float_digits behind your back.  Or maybe it's now asking for the
float value to be transmitted in binary, and then the decimal conversion
is being done on the Java side using who-knows-what rules.

Bottom line is that if you are using float anywhere along the line
in a calculation you expect exact results from, You're Doing It
Wrong.  It's not really that exciting to trace down exactly where
the roundoff is happening, because the end answer is still going to
be that you need to not do that.  Use numeric, or integer if you
know you're dealing with not-too-large integers.

            regards, tom lane