CODEWITHSUNDEEP
sqlpostgresqlcastingfloating-point
Shashank V C
Shashank V C

Reputation: 153

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.

Upvotes: 0

Views: 1806

Answers (1)

Abinash
Abinash

Reputation: 574

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

Upvotes: 1

Related Questions