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

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?

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
------------------------------------------------------------------

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.

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

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

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.

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!

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