nobs@nobswolf.info (Emil Obermayr) writes:
> select cast('8.000' as text) = 8.00 as test, 1 as case
> union
> select cast('8.000' as text) = 8.000, 2
> union
> select '8.00' = 8.000, 3
> Why is the string literal in case 3 parsed as float so the
> comparision is true.
The quoted literal is initially considered to be of "unknown" data type,
whereas the unquoted 8.000 is initially considered to be of type
numeric. Then the "unknown = numeric" operator is resolved as "numeric
= numeric", so the string literal is converted to numeric, and in the
numeric domain 8.00 and 8.000 compare equal.
I can tell that you are not running the latest PG release, btw, because
your first two examples fail entirely in 8.3:
regression=# select cast('8.000' as text) = 8.00 as test;
ERROR: operator does not exist: text = numeric
LINE 1: select cast('8.000' as text) = 8.00 as test;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
This is a consequence of having made numeric-to-text conversion not
happen without an explicit cast. In prior releases what you were
getting there was silent conversion of the numeric constant to text
followed by a textual comparison. That behavior led to lots of
confusion, and I think it's contributing to yours...
You can find more details in the "Type Conversion" section of the
manual.
regards, tom lane