SELECT with REAL...

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&gt; (or maybe
<http://docs.sun.com/source/806-3568/ncg_goldberg.html&gt;
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.