"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> However there still remains the following case.
> select * from num_data where val = 1.1;
> ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'
> You will have to retype this query using an explicit cast
Yeah. I'm not sure that that can be fixed without a major redesign of
the type-conversion hierarchy, which is not something I care to try
during beta ;-).
In fact, it's arguable that the system is doing the right thing by
forcing the user to specify whether he wants a NUMERIC or FLOAT8
comparison to be used. There are other examples where we *must*
refuse to decide. For example:
regression=# create table ff (f1 char(8), f2 varchar(20));
CREATE
regression=# select * from ff where f1 = f2;
ERROR: Unable to identify an operator '=' for types 'bpchar' and 'varchar' You will have to retype this query
usingan explicit cast
This is absolutely the right thing, because bpchar and varchar do not
have the same comparison semantics (trailing blanks are significant in
one case and not in the other), so the user has to tell us which he
wants.
> SQL standard seems to say 1.1 is a numeric constant and
> it's not good to treat a numeric value as an aproximate value.
> For example,what do you think about the following.
That argument is untenable. NUMERIC has limitations just as bad as
FLOAT's; they're merely different. For example:
regression=# select 1.0/300000.0; ?column?
----------------------3.33333333333333e-06
(1 row)
regression=# select 1.0::numeric / 300000.0::numeric; ?column?
--------------0.0000033333
(1 row)
Notice the completely unacceptable loss of precision ;-) in the second
case.
When you look at simple cases like "var = constant" it seems easy to
say that the system should just do the right thing, but in more complex
cases it's not always easy to know what the right thing is.
I think what you are proposing is to change the system's default
assumption about decimal constants from float8 to numeric. I think
that's a very risky change that is likely to break existing applications
(and if we throw in automatic conversions, it'll break 'em silently).
I'm not eager to do that.
regards, tom lane