Обсуждение: Error: Operator does not exist: "char"=integer
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.
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.
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.
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
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