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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-bugs(at)postgresql(dot)org, "jose soares" <jose(dot)soares(at)sferacarta(dot)com>
Subject: Re: BUG #6036: why a REAL should be queried as a string?
Date: 2011-05-24 16:24:35
Message-ID: 7675.1306254275@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2011-05-24 19:59:59 Re: [BUGS] BUG #6034: pg_upgrade fails when it should not.
Previous Message Kevin Grittner 2011-05-24 15:47:47 Re: BUG #6036: why a REAL should be queried as a string?