Re: BUG #6036: why a REAL should be queried as a string?

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> You're running into multiple issues here.  First off, you should
> understand that "real" is an approximate data type, which is not
> capable of exactly representing many decimal fractions.  "numeric"
> can exactly represent decimal fractions.  So, while it's OK when you
> compare real to numeric on values for which real happens to be
> exact, it falls apart when its approximation doesn't exactly match
> the numeric value:

Actually, the comparison constant is getting cast to float8, as can be
seen with "explain":

regression=# explain select * from frazione where importo='0.833';
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on frazione  (cost=0.00..36.75 rows=11 width=8)
   Filter: (importo = 0.833::real)
(2 rows)

regression=# explain select * from frazione where importo=0.833;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on frazione  (cost=0.00..36.75 rows=11 width=8)
   Filter: (importo = 0.833::double precision)
(2 rows)

But the basic point is correct: 0.833::float8 is a value that has no
exact match in float4 arithmetic.

regression=# select 0.833::float8 - 0.833::float4;
       ?column?
-----------------------
 -4.29153446113162e-09
(1 row)

In general, doing exact comparisons on float values is going to bite you
sooner or later, unless you know exactly what you're doing.

            regards, tom lane