Обсуждение: SELECT with REAL...
Hi,
I've never noticed before, but I don't manage to do a "SELECT" with
conditions on REAL columns !!
For example, the following command always returns 0 row, even if rows
with myreal=10.5 do exist :
SELECT * FROM mytable WHERE myreal=10.5;
Where am I wrong ?!
Thank you in advance,
Philippe Ferreira.
On Feb 6, 2006, at 7:30 , Philippe Ferreira wrote:
> For example, the following command always returns 0 row, even if
> rows with myreal=10.5 do exist :
> SELECT * FROM mytable WHERE myreal=10.5;
>
> Where am I wrong ?!
Without seeing a more complete example, it's hard to say. It works
for me here:
create table real_test
(
real_label text primary key
, real_value real not null unique
);
copy real_test (real_label, real_value) from stdin;
foo 1.2
bar 10.53
baz 10.5
bat -54.3
\.
test=# select * from real_test;
real_label | real_value
------------+------------
foo | 1.2
bar | 10.53
baz | 10.5
bat | -54.3
(4 rows)
test=# select * from real_test where real_value >= 10.5;
real_label | real_value
------------+------------
bar | 10.53
baz | 10.5
(2 rows)
test=# select * from real_test where real_value = 10.5;
real_label | real_value
------------+------------
baz | 10.5
(1 row)
test=# select version();
version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.1.0 on powerpc-apple-darwin8.3.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc.
build 5026)
(1 row)
Michael Glaesemann
grzm myrealbox com
On Feb 5, 2006, at 2:30 PM, Philippe Ferreira wrote: > Hi, > > I've never noticed before, but I don't manage to do a "SELECT" with > conditions on REAL columns !! > > For example, the following command always returns 0 row, even if > rows with myreal=10.5 do exist : > SELECT * FROM mytable WHERE myreal=10.5; > > Where am I wrong ?! Comparing two floating point numbers for equality seldom works the way you want it to. Without seeing the exact data you have it's hard to say for sure (as there may be some other issue with what you're doing) but I wouldn't expect this to work with data from arbitrary sources. Try SELECT * FROM mytable WHERE myreal >= 10.49 AND myreal <= 10.51; and see what result that gives. Cheers, Steve
> Comparing two floating point numbers for equality seldom works > the way you want it to. > > Without seeing the exact data you have it's hard to say for sure (as > there > may be some other issue with what you're doing) but I wouldn't expect > this to work with data from arbitrary sources. > > Try > > SELECT * FROM mytable WHERE myreal >= 10.49 AND myreal <= 10.51; > > and see what result that gives. > This way, it works... Philippe Ferreira.
Hi,
I've just realized that this way, it works very fine :
SELECT * FROM mytable WHERE myreal = 13.95::real;
But I still don't understand very well why I need the explicit
conversion (::real) ...
Philippe Ferreira.
On Feb 6, 2006, at 10:21 AM, Philippe Ferreira wrote: > >> Comparing two floating point numbers for equality seldom works >> the way you want it to. >> >> Without seeing the exact data you have it's hard to say for sure >> (as there >> may be some other issue with what you're doing) but I wouldn't expect >> this to work with data from arbitrary sources. >> >> Try >> >> SELECT * FROM mytable WHERE myreal >= 10.49 AND myreal <= 10.51; >> >> and see what result that gives. >> > This way, it works... Then the problem you're seeing isn't database-related, it's just due to the standard problem of misuse of floating-point numbers. You'll need to go back and look at the queries you're using and see what comparisons you really want your application to be doing. <http://www.lahey.com/float.htm> (or maybe <http://docs.sun.com/source/806-3568/ncg_goldberg.html> depending on your maths background) is well worth a read. Cheers, Steve
On 6 feb 2006, at 19.32, Philippe Ferreira wrote: > I've just realized that this way, it works very fine : > > SELECT * FROM mytable WHERE myreal = 13.95::real; > > But I still don't understand very well why I need the explicit > conversion (::real) ... Try this: SELECT 13.95 = 13.95::real; It should yield false, because the first number constant is presumed to be of type numeric, which is an exact format, and the second constant is explicitly cast to a single precision floating point number, in which it doesn't fit*, and therefore actually is stored as 13.9499998**. So, the comparison is in fact 13.95=13.9499998, which of course is false. To see the effect in another way, try: SELECT 13.95::real + 0.00000000000001; *) The reason it doesn't fit is that the floating point representation is using base 2, instead of base 10. **) The exact value could vary, depending on the floating point implementation of your system. This is what my implementation does. Sincerely, Niklas Johansson
Hi, Thank you all for your comments. Finally, I think I will simply use the data type NUMERIC instead of REAL in all my columns !! This type is more appropriate to store prices ! Philippe Ferreira. > > Try this: > > SELECT 13.95 = 13.95::real; > > It should yield false, because the first number constant is presumed > to be of type numeric, which is an exact format, and the second > constant is explicitly cast to a single precision floating point > number, in which it doesn't fit*, and therefore actually is stored as > 13.9499998**. So, the comparison is in fact 13.95=13.9499998, which > of course is false. > > To see the effect in another way, try: > > SELECT 13.95::real + 0.00000000000001; > > *) The reason it doesn't fit is that the floating point > representation is using base 2, instead of base 10. > **) The exact value could vary, depending on the floating point > implementation of your system. This is what my implementation does.