Re: data type of string literal

On Feb 26, 2008, at 3:42 , Emil Obermayr wrote:

> Why is the string literal in case 3 parsed as float so the
> comparision is true.

It's not a string literal: it's just a literal. The right hand side
of the comparison is a valid float representation (and not a valid
string representation), while the left hand side is both a valid
float representation and a valid string representation, so Postgres
is interpreting the left hand side as a float.

> While in the other cases the
> float literal is auto-casted to a string, so the
> comparision is only true if the number of zeros is equal?

In cases 1 and 2, you've explicitly cast the left hand side to text,
so Postgres will attempt to interpret the right hand side as text if
it can. This works in 8.2 (and prior versions), but 8.3 is more
strict and throws errors:


test=# 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.

test=# select cast('8.000' as text) = 8.000;
ERROR:  operator does not exist: text = numeric
LINE 1: select cast('8.000' as text) = 8.000;
                                      ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.

In 8.3, case 3 still works:

test=# select '8.00' = 8.000;
  ?column?
----------
  t
(1 row)

Michael Glaesemann
grzm seespotcode net