Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group