Re: SELECT with REAL...

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