Strange Type Mismatch on Insert

Jeff Eckermann <jeckermann@verio.net> writes:

Can anyone see what is the problem with the following insert?
gmp=# insert into data (site, gmp_id, item, category, gmp) values
('Rochester', 22, 'Design fee', 12, 40000.00);
ERROR: Unable to identify an operator '=' for types 'text' and 'int4'

Got any foreign keys associated with that table? Check for type
mismatch between referencing and referenced columns. It's a bug
that such problems are not detected when you declare the key
relationship, but right now they're not detected until runtime...

regards, tom lane

On Mon, 19 Mar 2001, Tom Lane wrote:

Jeff Eckermann <jeckermann@verio.net> writes:

Can anyone see what is the problem with the following insert?
gmp=# insert into data (site, gmp_id, item, category, gmp) values
('Rochester', 22, 'Design fee', 12, 40000.00);
ERROR: Unable to identify an operator '=' for types 'text' and 'int4'

Got any foreign keys associated with that table? Check for type
mismatch between referencing and referenced columns. It's a bug
that such problems are not detected when you declare the key
relationship, but right now they're not detected until runtime...

As an odd side note on that...

I have a fix, using oper() to look for an = operator. I noticed
while doing that though that varchar and int4 appear to be comparable
while text and int4 aren't which seemed rather odd.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:

I have a fix, using oper() to look for an = operator. I noticed
while doing that though that varchar and int4 appear to be comparable
while text and int4 aren't which seemed rather odd.

regression=# select '44'::varchar = 44::int4;
?column?
----------
t
(1 row)

regression=# select '44'::text = 44::int4;
ERROR: Unable to identify an operator '=' for types 'text' and 'int4'
You will have to retype this query using an explicit cast
regression=#

Bizarre. Investigation shows that the first case is handled as

varchareq('44'::varchar, varchar(44::int4))

where the conversion function is pg_proc OID 1619. The exact same
C function is also declared as text(int4), OID 112, but the system
won't make the comparable promotion in that case.

Upon stepping through oper_select_candidate, I find that the problem
is that text is *too* coercible. We have relatively few coercions
from varchar to something else, so the routine is fairly easily able
to choose a single candidate operator (it has only 2 candidates after
the first round of eliminations, and only 1 after the second). But
text has a ton of coercions to other types, meaning that 18 candidate
operators survive the first round, 5 the second and third rounds,
and even after round 4 there are two candidates left (text = text and
oid = int4, as it happens). So it fails to choose a unique candidate.

This is a little discouraging, since it raises the likelihood that
oper_select_candidate will break down entirely if we were to do any
wholesale extension of coercibility (like making text convertible to
anything via datatype input functions). We wouldn't have this failure
if there weren't an oid(text) coercion function ... now imagine what
happens if there's text-to-anything.

I've always been a tad bothered by the way we handle ambiguity
resolution, but I don't have a better answer to offer offhand.
Something to think about for the future.

regards, tom lane

Absolutely right. I've been editing my setup script, and a mistake crept
in. Obvious in retrospect, but then this is the first time I've actually
used referential integrity features...
Thanks for saving my sanity yet again!