Re: numeric and float comparison oddities

Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
> Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>> I saw some strange results:

> The part I find strange is that the first one evaluates to true,
> since numeric can exactly represent 1.1 and float8 cannot.

The reason is that the numeric input is converted to float8 for
comparison:

regression=# create table ttt(f4 float4, f8 float8, fn numeric);
CREATE TABLE
regression=# explain verbose select f4=fn, f8=fn from ttt;
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on public.ttt (cost=0.00..32.00 rows=1100 width=44)
Output: (f4 = (fn)::double precision), (f8 = (fn)::double precision)
Planning time: 0.325 ms
(3 rows)

Were it not, you'd hardly ever get equality.

I think that years ago we concluded this behavior was required by
SQL spec (see the language about exact vs inexact numeric types).

regards, tom lane