Search code examples
sqlpostgresqlcastingfloating-point

Summation of 2 floating point values gives incorrect result with higher precision

The sum of 2 floating values in postgres gives the result with higher precision. Expected:

669.05 + 1.64 = 670.69

Actual:

SELECT CAST(669.05 AS FLOAT) + CAST(1.64 AS FLOAT)
------------------
670.6899999999999

The result is having higher precision than expected.

The same operation with different set of inputs behaves differently.

SELECT CAST(669.05 AS FLOAT) + CAST(1.63 AS FLOAT)
------------------
670.68

Here I have reduced the problem statement by finding the 2 numbers for which the issue exists. The actual problem is when I do this on a whole table the result would be very big with higher precisions (depending on the values, and I do not have an explanation for exactly what/kind of values the precision shoots up) and we had to handle the scale in the application level. Example query

SELECT numeric_column_1/ CAST(numeric_column_2 AS FLOAT) FROM input_table;

Note: The behaviour is same for FLOAT(53) as well.

Solution

  • As per postgresql documentation, float uses inexact precision. Better to use DECIMAL or NUMERIC, which supports exact user specified precision.

    SELECT CAST(669.05 AS numeric) + CAST(1.64 AS numeric)

    Floating-Point Types in PostgreSQL

    The data types real and double precision are inexact, variable-precision numeric types. On all currently supported platforms, these types are implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.

    Numeric Types

    Name Storage Size Description Range
    smallint 2 bytes small-range integer -32768 to +32767
    integer 4 bytes typical choice for integer -2147483648 to +2147483647
    bigint 8 bytes large-range integer -9223372036854775808 to +9223372036854775807
    decimal variable user-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
    numeric variable user-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
    real 4 bytes variable-precision, inexact 6 decimal digits precision
    double precision 8 bytes variable-precision, inexact 15 decimal digits precision
    smallserial 2 bytes small autoincrementing integer 1 to 32767
    serial 4 bytes autoincrementing integer 1 to 2147483647
    bigserial 8 bytes large autoincrementing integer 1 to 9223372036854775807

    DB Fiddle: Try it here