Julius Tuskenis <julius@xxxxxxxx> writes: > we updated one of our systems from postgresql 8.2 to 8.3 and now some > queries are generating errors that no operator matches the given name > and argument type. That is not surprising as in v8.3 the automatic > casting was removed what troubles me is that sometimes it still casts What was removed was a small number of cases where it would implicitly cast non-string datatypes to text; which, as often as not, was wrong and resulted in surprising behavior. It is not correct to say that we got rid of implicit casts altogether. For example, in the case you give, > ... where msg_itemid = 0 ... > ERROR: operator does not exist: character varying = integer msg_itemid could have a value like '00'. It's not immediately obvious to the reader whether this should be considered equal to the integer constant 0. 8.3 and up now require you to either quote the constant (so that it can be considered a string rather than an integer), in which case you'd get textual comparison and '00' is different from '0'; or explicitly cast msg_itemid to integer, in which case you'd get numeric comparison and '00'::integer will be equal to 0. What you were getting before was silent use of textual comparison, which might or might not be what you really wanted. > select '1' = 1 results in true That's a completely different behavior: the quoted constant, which is initially considered of "unknown" type, gets resolved as integer because an integer is what it's being compared to. It's worth comparing this to what will happen if you fix your query as I suggest: ... where msg_itemid = '0' ... What really happens under the hood is that '0' is initially considered of unknown type, and then after the parser observes that it's being compared to a varchar variable, the constant's type is resolved as varchar, allowing the '=' operator to be resolved as varchar = varchar. This behavior is the same in all existing releases of Postgres. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
