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

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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