Обсуждение: operator does not exist

Hello,

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

select '1' = 1 results in true

while select sum(msg_price) from messages where msg_itemid = 0 results
in error:
ERROR:  operator does not exist: character varying = integer
LINE 1: select sum(msg_price) from messages where msg_itemid = 0

I will add the operator ant then we'll fix the queries, but is the first
example ok? Should it not raise error ?

--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050

Julius Tuskenis <julius@nsoft.lt> writes:

> select '1' = 1 results in true

This is a undecorated literal, which PostgreSQL will cast as integer
when it discovers that's what makes sense.

> while select sum(msg_price) from messages where msg_itemid = 0 results in
> error:
> ERROR:  operator does not exist: character varying = integer
> LINE 1: select sum(msg_price) from messages where msg_itemid = 0

Here, msg_itemid is known to be a varchar, and 0 is a numeric literal,
which is in the range of an integer, so the type is resolved as an
integer. Now the = operator does not exist for varchar, integer.

> I will add the operator ant then we'll fix the queries, but is the first
> example ok? Should it not raise error ?

Not as written. Try to decorate the literal to force PostgreSQL into
considering it of the type you have in mind:

  SELECT text '1' = 1;

Or even do a cast, this way:

  SELECT '1'::text = 1;

Regards,
--
dim

Thank you for your informative answer.


--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050

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