Skip site navigation (1) Skip section navigation (2)

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

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-bugs(at)postgresql(dot)org>, "jose soares" <jose(dot)soares(at)sferacarta(dot)com>
Subject: Re: BUG #6036: why a REAL should be queried as a string?
Date: 2011-05-24 15:47:47
Message-ID: 4DDB8CD3020000250003DBF2@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-bugs
"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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-05-24 16:24:35
Subject: Re: BUG #6036: why a REAL should be queried as a string?
Previous:From: manish singhDate: 2011-05-24 15:27:32
Subject: BUG #6037: error on restarting slave - cp: cannot stat `pg_xlog....`: no such file or ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group