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` ?

Accepted Answer

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.

Value of real type incorrectly compares

Solution

Problem