Postgres Pro
Downloads
Home   >   mailing lists

Thread: Type cast errors in version 9.2 while upgrade

Type cast errors in version 9.2 while upgrade

From
Arun P.L
Date:
Hi All,

We are doing a postgresql upgrade from version 7.4.30 to 9.2. While testing the queries in newer version, the following error is obtained for some of the queries which were working fine in older version.

"ERROR:  operator does not exist: character varying = integer.

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts."


When modified the script to add ' '(quotes) to integer values, the queries are working fine in 9.2 version also. Is this the best fix for this? or is there any other way (adding new type cast??) which will help to overcome this without modifying all the queries? Please advise.



Thanks for your help in advance,

Arun


Re: Type cast errors in version 9.2 while upgrade

From
Alban Hertroys
Date:
On 18 June 2013 14:38, Arun P.L <arunpl@hotmail.com> wrote:

Hi All,

We are doing a postgresql upgrade from version 7.4.30 to 9.2. While testing the queries in newer version, the following error is obtained for some of the queries which were working fine in older version.

"ERROR:  operator does not exist: character varying = integer.

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts."


When modified the script to add ' '(quotes) to integer values, the queries are working fine in 9.2 version also. Is this the best fix for this? or is there any other way (adding new type cast??) which will help to overcome this without modifying all the queries? Please advise.


The solution is to add an explicit type cast. Whether that's a type cast from integer to varchar or the other way around depends on your situation.

To cast the type, you can use the standard SQL function CAST() or the Postgres specific :: cast operator or you can do the cast before the values enter the query (like you seem to have done by putting quotes around the integer value).

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Re: Type cast errors in version 9.2 while upgrade

From
Andreas Kretschmer
Date:
Arun P.L <arunpl@hotmail.com> wrote:

> Hi All,
>
> We are doing a postgresql upgrade from version 7.4.30 to 9.2. While testing the
> queries in newer version, the following error is obtained for some of the
> queries which were working fine in older version.
>
>
> "ERROR:  operator does not exist: character varying = integer.
>
> HINT:  No operator matches the given name and argument type(s). You might need
> to add explicit type casts."

That's a problem since 8.2 -> 8.3, now we don't have a lot of implicit casts. You
can redefine that, read:
http://petereisentraut.blogspot.de/2008/03/readding-implicit-casts-in-postgresql.html

But i think you should redesign your code ...



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°