Re: Re: BUG #4078: ERROR: operator does not exist: numeric = character varying

Eric Haszlakiewicz <erh(at)swapsimple(dot)com> writes:
> I created this, which seems to solve the problem:

> create function casting_eq_operator(integer, "char")
> returns boolean as 'begin
> return $1 = cast ($2 as integer);
> end;' language plpgsql immutable strict;

> CREATE OPERATOR = (PROCEDURE = casting_eq_operator,
> LEFTARG = integer , RIGHTARG = "char",
> COMMUTATOR = =, NEGATOR = !=, HASHES, MERGES
> );

> Can this be included by default?

No. Even if we desired to reverse the decision about not having
implicit casting behavior, this definition of the operator would not be
appropriate because it provides the opposite of the old behavior.
The pre-8.3 behavior would have been to cast the integer to text and
apply a textual comparison; which gives different comparison behavior,
eg leading zeroes in the string would affect the result. Not to mention
that the cast to integer in this definition would fail outright if the
string didn't look like an integer.

A large part of the reasoning for getting rid of the implicit casts
was exactly that it's not very clear what a comparison of this sort
should act like, and most people who are accidentally invoking it
haven't thought that through either.

(Some other problems: I'm pretty sure you meant to refer to text or
varchar not "char"; you referenced commutator and negator operators
without defining them; this operator certainly does not hash, and
I don't think it merges either, though maybe you could make the latter
work if you'd provided all the requisite btree-opfamily infrastructure.)

regards, tom lane