Re: BUG #6036: why a REAL should be queried as a string?

"jose soares" <jose(dot)soares(at)sferacarta(dot)com> wrote:

> Description: why a REAL should be queried as a string?

> I'd like to to report this strange behavior, that I think is a
> bug...
>
> # \d frazione
> Table "public.frazione"
> Column | Type | Modifiers
> ------------+---------+--------------------
> id | integer | not null
> importo | real | not null
>
> # update frazione set importo=0.833 where id=549;
> UPDATE 1
> # select * from frazione where importo=0.833;
> id | importo
> ----+---------
> (0 rows)
>
> # select * from frazione where importo='0.833';
> id | importo
> -----+----------
> 549 | 0.833
> (1 rows)

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:

test=# select '0.125'::numeric = '0.125'::real;
?column?
----------
t
(1 row)

test=# select '0.833'::numeric = '0.833'::real;
?column?
----------
f
(1 row)

Unadorned, that literal is taken as numeric:

test=# select pg_typeof(0.833);
pg_typeof
-----------
numeric
(1 row)

The other issue is that in PostgreSQL a quoted literal is not
initially a character string literal -- it starts out as type
"unknown" to make it easier to specify literals for various types.

test=# select pg_typeof('0.833');
pg_typeof
-----------
unknown
(1 row)

In your second example (where the number matches), the unknown value
is cast to real when you compare to the real column, so the same
approximation is generated.

In general it is a bad idea to compare for equality using
approximate values. Perhaps you would be better off with that
column defined as numeric?

-Kevin