Thread: data type of string literal

Hi List,

I just joined because I found a behaviour of implicit casts I don't
understand. I am quite new to Postgre, trying out things and
tried this sql-code:

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

This gives me the following result:

test => f    case => 1
test => t    case => 2
test => t    case => 3

Why is the string literal in case 3 parsed as float so the
comparision is true. 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?

Is there a exact column type that fits a string literal? Explicit
casts to char or varchar instead of text gives the same results.

Yours,

Emil 'nobs' Obermayr

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

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