I'm trying to delete all the records or only one record or insert one
record in a table but
i'm having this message:
ERROR:  Unable to identify an operator '=' for types 'int4' and 'text'
        You will have to retype this query using an explicit cast

What's this means ???

Thanks

Luis Sousa


Thu, 14 Dec 2000 15:33:27 +0000
Luis Sousa <llsousa@ualg.pt>
Ocasional problems !!!!
> i'm having this message:
> ERROR:  Unable to identify an operator '=' for types 'int4' and 'text'
>         You will have to retype this query using an explicit cast

Without knowing your schema and query, we can't tell you exactly. But
your query is trying to compare a string to an integer, which you can do
by using an explicit cast. For example,

  select text '123' = 123;

will fail, but

  select cast((text '123') as integer) = 123;

will succeed.

                    - Thomas

Thu, 14 Dec 2000 16:14:53 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Ocasional problems !!!!

What is the schema of the table involved and what are the queries you
are trying to run?

Stephan Szabo
sszabo@bigpanda.com

On Thu, 14 Dec 2000, Luis Sousa wrote:

> I'm trying to delete all the records or only one record or insert one
> record in a table but
> i'm having this message:
> ERROR:  Unable to identify an operator '=' for types 'int4' and 'text'
>         You will have to retype this query using an explicit cast
> 
> What's this means ???
> 
> Thanks
> 
> Luis Sousa
> 


Thu, 14 Dec 2000 10:07:57 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Ocasional problems !!!!
I just trying to execute a simple query in a table to delete a simpe record
or all of them, like:

DELETE * FROM table;

I have a schema of more or less 25 tables, that are created using a script.
When i'm trying to use that table (and only happens in this table) when
created by the script i receive the message below.
The most strange is that i droped the table and i created again,
maintaining the structure created with the script and i didn't had any
problems !!!

Best Regards

Luis Sousa


Stephan Szabo wrote:

> What is the schema of the table involved and what are the queries you
> are trying to run?
>
> Stephan Szabo
> sszabo@bigpanda.com
>
> On Thu, 14 Dec 2000, Luis Sousa wrote:
>
> > I'm trying to delete all the records or only one record or insert one
> > record in a table but
> > i'm having this message:
> > ERROR:  Unable to identify an operator '=' for types 'int4' and 'text'
> >         You will have to retype this query using an explicit cast
> >
> > What's this means ???
> >
> > Thanks
> >
> > Luis Sousa
> >


Fri, 15 Dec 2000 09:33:07 +0000
Luis Sousa <llsousa@ualg.pt>
Re: Ocasional problems !!!!
I received that message working directly in psql.

I already send another email, please see.

Regards

Luis Sousa


Thomas Lockhart wrote:

> > But i'm not making any compare.
> > I just wrote delete from table; and i receive that message.
>
> Hmm. We will need to know more about your setup, since a simple
>
>   delete from table;
>
> in psql does not involve *any* comparisons, and should never provoke the
> message you are receiving.
>
> If you think that you are doing something as simple as that, then
> perhaps the application you are using is doing extra stuff underneath?
>
>                     - Thomas


Fri, 15 Dec 2000 09:46:00 +0000
Luis Sousa <llsousa@ualg.pt>
Re: Ocasional problems !!!!
>>>> But i'm not making any compare.
>>>> I just wrote delete from table; and i receive that message.
>> 
>> Hmm. We will need to know more about your setup, since a simple
>> 
>> delete from table;
>> 
>> in psql does not involve *any* comparisons, and should never provoke the
>> message you are receiving.

Perhaps there is a foreign key constraint, or some such, being invoked
by this command?

			regards, tom lane

Fri, 15 Dec 2000 10:59:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Ocasional problems !!!! 

Did you perhaps have foreign key constraints with an on delete
clause defined on a table that referenced this one?  Postgres doesn't
currently check that the types are comparable before making the
constraint.  I'm working on adding a check for that now.

Or for that matter, any other rules or triggers could do it.

On Fri, 15 Dec 2000, Luis Sousa wrote:

> I just trying to execute a simple query in a table to delete a simpe record
> or all of them, like:
> 
> DELETE * FROM table;
> 
> I have a schema of more or less 25 tables, that are created using a script.
> When i'm trying to use that table (and only happens in this table) when
> created by the script i receive the message below.
> The most strange is that i droped the table and i created again,
> maintaining the structure created with the script and i didn't had any
> problems !!!
> 
> Best Regards
> 
> Luis Sousa
> 
> 
> Stephan Szabo wrote:
> 
> > What is the schema of the table involved and what are the queries you
> > are trying to run?
> >
> > Stephan Szabo
> > sszabo@bigpanda.com
> >
> > On Thu, 14 Dec 2000, Luis Sousa wrote:
> >
> > > I'm trying to delete all the records or only one record or insert one
> > > record in a table but
> > > i'm having this message:
> > > ERROR:  Unable to identify an operator '=' for types 'int4' and 'text'
> > >         You will have to retype this query using an explicit cast
> > >
> > > What's this means ???
> > >
> > > Thanks
> > >
> > > Luis Sousa
> > >
> 


Fri, 15 Dec 2000 09:17:47 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Ocasional problems !!!!
I really have constraints of foreign keys but not on delete, only on update

Stephan Szabo wrote:

> Did you perhaps have foreign key constraints with an on delete
> clause defined on a table that referenced this one?  Postgres doesn't
> currently check that the types are comparable before making the
> constraint.  I'm working on adding a check for that now.
>
> Or for that matter, any other rules or triggers could do it.
>
> On Fri, 15 Dec 2000, Luis Sousa wrote:
>
> > I just trying to execute a simple query in a table to delete a simpe record
> > or all of them, like:
> >
> > DELETE * FROM table;
> >
> > I have a schema of more or less 25 tables, that are created using a script.
> > When i'm trying to use that table (and only happens in this table) when
> > created by the script i receive the message below.
> > The most strange is that i droped the table and i created again,
> > maintaining the structure created with the script and i didn't had any
> > problems !!!
> >
> > Best Regards
> >
> > Luis Sousa
> >
> >
> > Stephan Szabo wrote:
> >
> > > What is the schema of the table involved and what are the queries you
> > > are trying to run?
> > >
> > > Stephan Szabo
> > > sszabo@bigpanda.com
> > >
> > > On Thu, 14 Dec 2000, Luis Sousa wrote:
> > >
> > > > I'm trying to delete all the records or only one record or insert one
> > > > record in a table but
> > > > i'm having this message:
> > > > ERROR:  Unable to identify an operator '=' for types 'int4' and 'text'
> > > >         You will have to retype this query using an explicit cast
> > > >
> > > > What's this means ???
> > > >
> > > > Thanks
> > > >
> > > > Luis Sousa
> > > >
> >

--
Luis Sousa
Tecnico Superior de Informatica
Gabinete de Assessoria e Planeamento
Universidade do Algarve




Mon, 18 Dec 2000 09:16:42 +0000
Luis Sousa <llsousa@ualg.pt>
Re: Ocasional problems !!!!

Actually, it's not going to matter since all foreign keys have a delete
portion (realized after seeing your response) that checks to make sure
the one you are deleting is not being referenced.
I'm surprised you're not seeing this on inserts into the fk table or
on updates to the pk table.  What are the types of the columns on 
both tables?

Stephan Szabo
sszabo@bigpanda.com

On Mon, 18 Dec 2000, Luis Sousa wrote:

> I really have constraints of foreign keys but not on delete, only on update
> 
> Stephan Szabo wrote:
> 
> > Did you perhaps have foreign key constraints with an on delete
> > clause defined on a table that referenced this one?  Postgres doesn't
> > currently check that the types are comparable before making the
> > constraint.  I'm working on adding a check for that now.
> >
> > Or for that matter, any other rules or triggers could do it.
> >
> > On Fri, 15 Dec 2000, Luis Sousa wrote:
> >
> > > I just trying to execute a simple query in a table to delete a simpe record
> > > or all of them, like:
> > >
> > > DELETE * FROM table;
> > >
> > > I have a schema of more or less 25 tables, that are created using a script.
> > > When i'm trying to use that table (and only happens in this table) when
> > > created by the script i receive the message below.
> > > The most strange is that i droped the table and i created again,
> > > maintaining the structure created with the script and i didn't had any
> > > problems !!!
> > >
> > > Best Regards
> > >
> > > Luis Sousa
> > >
> > >
> > > Stephan Szabo wrote:
> > >
> > > > What is the schema of the table involved and what are the queries you
> > > > are trying to run?
> > > >
> > > > Stephan Szabo
> > > > sszabo@bigpanda.com
> > > >
> > > > On Thu, 14 Dec 2000, Luis Sousa wrote:
> > > >
> > > > > I'm trying to delete all the records or only one record or insert one
> > > > > record in a table but
> > > > > i'm having this message:
> > > > > ERROR:  Unable to identify an operator '=' for types 'int4' and 'text'
> > > > >         You will have to retype this query using an explicit cast
> > > > >
> > > > > What's this means ???
> > > > >
> > > > > Thanks
> > > > >
> > > > > Luis Sousa
> > > > >
> > >
> 
> --
> Luis Sousa
> Tecnico Superior de Informatica
> Gabinete de Assessoria e Planeamento
> Universidade do Algarve
> 
> 
> 



Mon, 18 Dec 2000 09:19:45 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Ocasional problems !!!!
I think i already discovered what's the problem !! At least the problem is not
happening again.

It was some problems in some triggers that are implementend in the database.

Anyway, i appreciatte all the time that you took with my problem



Best Regards

Luis Sousa


Stephan Szabo wrote:

> Actually, it's not going to matter since all foreign keys have a delete
> portion (realized after seeing your response) that checks to make sure
> the one you are deleting is not being referenced.
> I'm surprised you're not seeing this on inserts into the fk table or
> on updates to the pk table.  What are the types of the columns on
> both tables?
>
> Stephan Szabo
> sszabo@bigpanda.com
>
> On Mon, 18 Dec 2000, Luis Sousa wrote:
>
> > I really have constraints of foreign keys but not on delete, only on update
> >
> > Stephan Szabo wrote:
> >
> > > Did you perhaps have foreign key constraints with an on delete
> > > clause defined on a table that referenced this one?  Postgres doesn't
> > > currently check that the types are comparable before making the
> > > constraint.  I'm working on adding a check for that now.
> > >
> > > Or for that matter, any other rules or triggers could do it.
> > >
> > > On Fri, 15 Dec 2000, Luis Sousa wrote:
> > >
> > > > I just trying to execute a simple query in a table to delete a simpe record
> > > > or all of them, like:
> > > >
> > > > DELETE * FROM table;
> > > >
> > > > I have a schema of more or less 25 tables, that are created using a script.
> > > > When i'm trying to use that table (and only happens in this table) when
> > > > created by the script i receive the message below.
> > > > The most strange is that i droped the table and i created again,
> > > > maintaining the structure created with the script and i didn't had any
> > > > problems !!!
> > > >
> > > > Best Regards
> > > >
> > > > Luis Sousa
> > > >
> > > >
> > > > Stephan Szabo wrote:
> > > >
> > > > > What is the schema of the table involved and what are the queries you
> > > > > are trying to run?
> > > > >
> > > > > Stephan Szabo
> > > > > sszabo@bigpanda.com
> > > > >
> > > > > On Thu, 14 Dec 2000, Luis Sousa wrote:
> > > > >
> > > > > > I'm trying to delete all the records or only one record or insert one
> > > > > > record in a table but
> > > > > > i'm having this message:
> > > > > > ERROR:  Unable to identify an operator '=' for types 'int4' and 'text'
> > > > > >         You will have to retype this query using an explicit cast
> > > > > >
> > > > > > What's this means ???
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > > Luis Sousa
> > > > > >
> > > >
> >
> > --
> > Luis Sousa
> > Tecnico Superior de Informatica
> > Gabinete de Assessoria e Planeamento
> > Universidade do Algarve
> >
> >
> >

--
Luis Sousa
Tecnico Superior de Informatica
Gabinete de Assessoria e Planeamento
Universidade do Algarve




Wed, 20 Dec 2000 15:41:42 +0000
Luis Sousa <llsousa@ualg.pt>
Re: Ocasional problems !!!!