Thread: Error: Operator does not exist: "char"=integer

Error: Operator does not exist: "char"=integer

From
novnov
Date:
I have restored a postgres 8.2.4-1 db onto a postgres 8.3.1-1 server, and
when I try to work with a table I get this error:

Error: Operator does not exist: "char" = integer

Hopefully that is enough of a clue to be useful. Maybe this is the first
time I've tried moving one of my non-trivial pg projects to a significantly
different version of postgres; is there a conversion process that helps with
moving between versions?
--
View this message in context:
http://www.nabble.com/Error%3A-Operator-does-not-exist%3A-%22char%22%3Dinteger-tp21067261p21067261.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: Error: Operator does not exist: "char"=integer

From
"Scott Marlowe"
Date:
On Wed, Dec 17, 2008 at 10:26 PM, novnov <novnovice@gmail.com> wrote:
>
> I have restored a postgres 8.2.4-1 db onto a postgres 8.3.1-1 server, and
> when I try to work with a table I get this error:
>
> Error: Operator does not exist: "char" = integer

What's the schema of the table, and the query that is failing?

Re: Error: Operator does not exist: "char"=integer

From
Raymond O'Donnell
Date:
On 18/12/2008 05:26, novnov wrote:
> I have restored a postgres 8.2.4-1 db onto a postgres 8.3.1-1 server, and
> when I try to work with a table I get this error:
>
> Error: Operator does not exist: "char" = integer

A lot of previously automatic casts were removed in the 8.3 series -
this is possibly one of them.

You now need to cast explicitly in such cases, e.g.

  select '5'::integer;

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Re: Error: Operator does not exist: "char"=integer

From
Peter Eisentraut
Date:
novnov wrote:
> I have restored a postgres 8.2.4-1 db onto a postgres 8.3.1-1 server, and
> when I try to work with a table I get this error:
>
> Error: Operator does not exist: "char" = integer
>
> Hopefully that is enough of a clue to be useful. Maybe this is the first
> time I've tried moving one of my non-trivial pg projects to a significantly
> different version of postgres; is there a conversion process that helps with
> moving between versions?

Yes, reading the release notes. ;-)  I think you will find your problem
explained there.

Re: Error: Operator does not exist: "char"=integer

From
Julius Tuskenis
Date:
Raymond O'Donnell rašė:
> A lot of previously automatic casts were removed in the 8.3 series -
> this is possibly one of them.
>
> You now need to cast explicitly in such cases, e.g.
>
>   select '5'::integer;
>
> Ray.
>
That is a good advice and a good practice. But the solution usually
takes time. For a quick (temporary) solution you could write your own
operator for handling "char" = integer cases.

I had to do it once for an "integer ILIKE text" operator:

CREATE OR REPLACE FUNCTION of_integer_ilike_text(prm_integer integer,
prm_text text)
  RETURNS boolean AS
$BODY$BEGIN
RETURN prm_integer::text ~~* prm_text ;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION of_integer_ilike_text(integer, text) OWNER TO useris;

CREATE OPERATOR ~~(
  PROCEDURE = of_integer_ilike_text,
  LEFTARG = int4,
  RIGHTARG = text);

I'm sure you'll manage to do this for "text = integer";

Julius Tuskenis



Re: Error: Operator does not exist: "char"=integer

From
novnov
Date:
The error went away; I updated the admin tool I use (pg lightning admin) and
that seemed to help.


Thanks everyone.


Julius Tuskenis-2 wrote:
>
> Raymond O'Donnell rašė:
>> A lot of previously automatic casts were removed in the 8.3 series -
>> this is possibly one of them.
>>
>> You now need to cast explicitly in such cases, e.g.
>>
>>   select '5'::integer;
>>
>> Ray.
>>
> That is a good advice and a good practice. But the solution usually
> takes time. For a quick (temporary) solution you could write your own
> operator for handling "char" = integer cases.
>
> I had to do it once for an "integer ILIKE text" operator:
>
> CREATE OR REPLACE FUNCTION of_integer_ilike_text(prm_integer integer,
> prm_text text)
>   RETURNS boolean AS
> $BODY$BEGIN
> RETURN prm_integer::text ~~* prm_text ;
> END;$BODY$
>   LANGUAGE 'plpgsql' VOLATILE
>   COST 100;
> ALTER FUNCTION of_integer_ilike_text(integer, text) OWNER TO useris;
>
> CREATE OPERATOR ~~(
>   PROCEDURE = of_integer_ilike_text,
>   LEFTARG = int4,
>   RIGHTARG = text);
>
> I'm sure you'll manage to do this for "text = integer";
>
> Julius Tuskenis
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>

--
View this message in context:
http://www.nabble.com/Error%3A-Operator-does-not-exist%3A-%22char%22%3Dinteger-tp21067261p21079266.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: Error: Operator does not exist: "char"=integer

From
Aarni
Date:
On Thursday 18 December 2008 12:46:38 Peter Eisentraut wrote:
> novnov wrote:
> > I have restored a postgres 8.2.4-1 db onto a postgres 8.3.1-1 server, and
> > when I try to work with a table I get this error:
> >
> > Error: Operator does not exist: "char" = integer
> >
> > Hopefully that is enough of a clue to be useful. Maybe this is the first
> > time I've tried moving one of my non-trivial pg projects to a
> > significantly different version of postgres; is there a conversion
> > process that helps with moving between versions?
>
> Yes, reading the release notes. ;-)  I think you will find your problem
> explained there.

Hi,

I had similar errors here and there after moving to 8.3.3 from 8.2.x., no more
automatic casts.

"ERROR:  operator does not exist: character varying = integer at character 286
HINT:  No operator matches the given name and argument type(s). You might need
to add explicit type casts."

Quick fix to sql statements eg.

... WHERE CAST (your_char AS INTEGER) = integer ...
... WHERE CAST (your_char AS INTEGER) IN (1,2,3,...)

BR,

--
Aarni Ruuhimäki


Re: Error: Operator does not exist: "char"=integer

From
Gregory Stark
Date:
Aarni <aarni@kymi.com> writes:

> "ERROR:  operator does not exist: character varying = integer at character 286
> HINT:  No operator matches the given name and argument type(s). You might need
> to add explicit type casts."
>
> Quick fix to sql statements eg.
>
> ... WHERE CAST (your_char AS INTEGER) = integer ...
> ... WHERE CAST (your_char AS INTEGER) IN (1,2,3,...)

Note that this is *not* what was happening in 8.2. There it was casting them
to text and doing a text comparison. In the case of integer and equality
they're probably equivalent. However < and > will behave quite differently.
That's why the casts disappeared -- you probably weren't running the queries
you thought you were running in 8.2 and previously.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Re: Error: Operator does not exist: "char"=integer

From
Aarni
Date:
On Friday 19 December 2008 01:29:06 you wrote:
> Aarni <aarni@kymi.com> writes:
> > "ERROR:  operator does not exist: character varying = integer at
> > character 286 HINT:  No operator matches the given name and argument
> > type(s). You might need to add explicit type casts."
> >
> > Quick fix to sql statements eg.
> >
> > ... WHERE CAST (your_char AS INTEGER) = integer ...
> > ... WHERE CAST (your_char AS INTEGER) IN (1,2,3,...)
>
> Note that this is *not* what was happening in 8.2. There it was casting
> them to text and doing a text comparison. In the case of integer and
> equality they're probably equivalent. However < and > will behave quite
> differently. That's why the casts disappeared -- you probably weren't
> running the queries you thought you were running in 8.2 and previously.

Hi Gregory,

Hmm, yes I did note this. Afterwards, though.

"Previously ... was automatically cast to text, for most (though not all)
built-in data type ... automatic casts too often caused surprising behavior."

Luckily enough, I did not get funny or unexpected results with the few queries
in one particular application I had to fix for 8.3.3.

Best regards,

--
Aarni Ruuhimäki