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.
Advertisement
Answer
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