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

Peter Eisentraut, April 1 2008
> Am Dienstag, 1. April 2008 schrieb rupesh:
> > ERROR:  operator does not exist: numeric = character varying at character
> > 675
> > HINT:  No operator matches the given name and argument type(s). You might
> > need to add explicit type casts.
> > (0.735 sec)
> >
> >
> > This was previously working in 8.2.3 but not in 8.3
> This was an intentional change.  Please read the release notes about fixing
> your code.

I read the release notes, and the idea of removing this to avoid problematic
automatic casts seems reasonable, but why not add an additional operator
so the useful cases actually still work?

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?

On a related note, I originally wrote this using 'character varying'
instead of '"char"', but that caused simple string comparisons in queries
to fail.  e.g.
select * from foo where mycol = 'abc';
when mycol is a varchar column complained about "invalid input syntax
 for integer".  Is there a simple explanation for why it wouldn't just
try to do a plain string comparison instead of using my custom operator?

eric

Eric Haszlakiewicz <erh@swapsimple.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

On Mon, Oct 13, 2008 at 09:10:41PM -0400, Tom Lane wrote:
> Eric Haszlakiewicz <erh@swapsimple.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.

    hmm.. I was thinking that a comparison between a number and a string
would cause an error if the string wasn't parseable.   Now that I
think about it more, I can see that having it just return false might
be reasonable too.

>
> (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.)

Yeah, you're right.  I didn't think that through entirely.
Sorry for the noise.

eric