Re: Problem with datatype REAL using the = (EQUAL) operator

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Javier Carlos Rivera
Your email address : fjcarlos ( at ) correo ( dot ) insp ( dot ) mx

System Configuration
----------------------
Architecture (example: Intel Pentium) : Intel Pentium 4

Operating System (example: Linux 2.0.26 ELF) : Debian GNU/Linux 3.0
2.4.23

RAM : 256 MB

PostgreSQL version (example: PostgreSQL-6.3.2) : PostgreSQL-7.4.1

Compiler used (example: gcc 2.7.2) : 2.95.4

Please enter a FULL description of your problem:
-------------------------------------------------
When I make a select and in the WHERE section I use the = (EQUAL)
operator whith a column of real datatype the results of the query is 0
rows even if there exist rows that match the condition.

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
-----------------------------------------------------------------------
bd_temporal=> CREATE TABLE tbl_temp (var real);
bd_temporal=> \d tbl_temp
Table "public.tbl_temp"
Column | Type | Modifiers
--------+------+-----------
var | real |

bd_temporal=> INSERT INTO tbl_temp VALUES(0.1);
bd_temporal=> INSERT INTO tbl_temp VALUES(0.11);
bd_temporal=> INSERT INTO tbl_temp VALUES(0.20);
bd_temporal=> INSERT INTO tbl_temp VALUES(0.25);
bd_temporal=> INSERT INTO tbl_temp VALUES(0.26);

bd_temporal=> SELECT * FROM tbl_temp WHERE var < 0.20;
var
------
0.1
0.11
(2 rows)
bd_temporal=> SELECT * FROM tbl_temp WHERE var = 0.11;
var
-----
(0 rows)
bd_temporal=> SELECT * FROM tbl_temp WHERE var = '0.11';
var
------
0.11
(1 row)

If you know how this problem might be fixed, list the solution below:
----------------------------------------------------------------------
For now I have to put between '' the value as I was working whith CHARs
values.
The weird thing is that with all the other comparison operator all works
well, only the = (EQUAL) operator makes things suchs as this:

bd_temporal=> SELECT * FROM tbl_temp WHERE var = 0.25;
var
------
0.25
(1 row)

bd_temporal=> SELECT * FROM tbl_temp WHERE var = 0.26;
var
-----
(0 rows)

bd_temporal=> SELECT * FROM tbl_temp WHERE var = '0.25';
var
------
0.25
(1 row)

bd_temporal=> SELECT * FROM tbl_temp WHERE var = '0.26';
var
------
0.26
(1 row)

-------------------------------------------------

"Javier Carlos" <javier(at)evaloportunidades(dot)insp(dot)mx> writes:
> When I make a select and in the WHERE section I use the = (EQUAL)
> operator whith a column of real datatype the results of the query is 0
> rows even if there exist rows that match the condition.

This isn't a bug, it's a natural consequence of the limited precision of
the float4 datatype. The constant "0.11" defaults to type double
precision, and there is no float4 value that exactly equals the double
precision value of 0.11. Try casting the constant to float4 explicitly,
or putting quotes around it.

regards, tom lane

On Thu, Feb 12, 2004 at 12:46:27 -0600,
Javier Carlos <javier(at)evaloportunidades(dot)insp(dot)mx> wrote:
> ============================================================================
> bd_temporal=> SELECT * FROM tbl_temp WHERE var < 0.20;
> var
> ------
> 0.1
> 0.11
> (2 rows)
> bd_temporal=> SELECT * FROM tbl_temp WHERE var = 0.11;
> var
> -----
> (0 rows)
> bd_temporal=> SELECT * FROM tbl_temp WHERE var = '0.11';
> var
> ------
> 0.11
> (1 row)
>
>
> If you know how this problem might be fixed, list the solution below:

If you want exact fractional numbers you should be using numeric, not float.
The problems you are seeing has to do with single precision and double
precision versions of .11 not being equal.