To: Julius Tuskenis <julius@nsoft.lt>
cc: pgsql-admin@postgresql.org
Subject: Re: operator does not exist 
In-reply-to: <4BA32695.1090402@nsoft.lt> 
References: <4BA32695.1090402@nsoft.lt>
Comments: In-reply-to Julius Tuskenis <julius@nsoft.lt>
	message dated "Fri, 19 Mar 2010 09:24:05 +0200"
Date: Fri, 19 Mar 2010 08:33:27 -0400
Message-ID: <29017.1269002007@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Julius Tuskenis <julius@nsoft.lt> 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