Postgres Pro
Downloads
Home   >   mailing lists

Thread: Strange Type Mismatch on Insert

Strange Type Mismatch on Insert

From
Jeff Eckermann
Date:
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'
        You will have to retype this query using an explicit cast
gmp=# \d data
                    Table "data"
 Attribute |     Type      |        Modifier
-----------+---------------+------------------------
 site      | text          | not null
 gmp_id    | integer       | not null default 9999
 co_id     | text          | not null default 'N/A'
 item      | text          | not null
 category  | integer       |
 gmp       | numeric(12,2) | default 0
 co_status | char(1)       |
Index: data_pkey

jeffe@kiyoko=> psql -V
psql (PostgreSQL) 7.0.0
jeffe@kiyoko=> uname -a
FreeBSD kiyoko.la.verio.net 4.0-STABLE FreeBSD 4.0-STABLE #0: Thu Apr 27
10:44:07 CDT 2000

Re: Strange Type Mismatch on Insert

From
Tom Lane
Date:
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

Re: Strange Type Mismatch on Insert

From
Stephan Szabo
Date:
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.

Re: Strange Type Mismatch on Insert

From
Tom Lane
Date:
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

RE: Strange Type Mismatch on Insert

From
Jeff Eckermann
Date:
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!

> -----Original Message-----
> From:    Tom Lane [SMTP:tgl@sss.pgh.pa.us]
> Sent:    Monday, March 19, 2001 6:27 PM
> To:    Jeff Eckermann
> Cc:    'pgsql-general@postgresql.org'
> Subject:    Re: [GENERAL] 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