SEO

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org