Value of real type incorrectly compares

Question

I have field of REAL type in db. I use PostgreSQL. And the query

SELECT * FROM my_table WHERE my_field = 0.15

does not return rows in which the value of my_field is 0.15.

But for instance the query

SELECT * FROM my_table WHERE my_field > 0.15

works properly.

How can I solve this problem and get the rows with my_field = 0.15 ?

Answer 1

To solve your problem use the data type numeric instead, which is not a floating point type, but an arbitrary precision type.

If you enter the numeric literal 0.15 into a numeric (same word, different meaning) column, the exact amount is stored - unlike with a real or float8 column, where the value is coerced to next possible binary approximation. This may or may not be exact, depending on the number and implementation details. The decimal number 0.15 happens to fall between possible binary representations and is stored with a tiny error.

Note that the result of a calculation can be inexact itself, so be still wary of the = operator in such cases.

It also depends how you test. When comparing, Postgres coerces diverging numeric types to a type that can best hold the result. Consider this demo:

CREATE TABLE t(num_r real, num_n numeric);
INSERT INTO t VALUES (0.15, 0.15);

SELECT num_r, num_n  
     , num_r = num_n       AS test1           --> FALSE
     , num_r = num_n::real AS test2           --> TRUE
     , num_r - num_n       AS result_nonzero  --> float8
     , num_r - num_n::real AS result_zero     --> real
FROM   t;

db<>fiddle here
_{Old sqlfiddle}

Therefore, if you have entered 0.15 as numeric literal into your column of data type real, you can find all such rows with:

SELECT * FROM my_table WHERE my_field = real '0.15'

Use numeric columns if you need to store fractional digits exactly.

Answer 2

Your problem originates from IEEE 754.

0.15 is not 0.15, but 0.15000000596046448 (assuming double precision), as it can not be exactly represented as a binary floating point number.

(check this calculator)

Why is this a problem? In this case, most likely because the other side of the comparison uses the exact value 0.15 - through an exact representation, like a numeric type. (Cleared up on suggestion by Eric)

So there are two ways:

• use a format that actually stores the numbers in decimal format - as Erwin suggested
  • (or at least use the same type across the board)
• use rounding as Jack suggested - which has to be used carefully (by the way this uses a numeric type too, to exactly represent 0.15...)

Recommended reading: What Every Computer Scientist Should Know About Floating-Point Arithmetic

_{(Sorry for the terse answer...)}