Postgresql Community:
We have an Java/JDBC application that runs against a range of versions
of Postgresql from 7.4 though 8.3 and are now moving to 8.4.
Because our databases will never approach 4GB in size we still use OIDs
... that is, in newer versions of Postgresql we create OIDs on all
tables with:
SET default_with_oids = true;
In Postgresql 8.4, however, we are seeing the following error:
SQL error executing statement.
update status set some_column = 'some_value' where oid = 'some_string'
org.postgresql.util.PSQLException: ERROR: operator does not exist: oid =
character varying
Hint: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
Position: 132
Because these SQL commands are generated on the fly by a Java
application that opens a JDBC connection to our database, we would
rather not use explicit casting and would prefer to add appropriate
casts in our Postgresql 8.4 databases to match the behavior that we get
in Postgresql 7.4/8.0/8.1/8.2/8.3 databases.
When I issue the command "\dC" and look for the casts that would convert
an OID to character varying, in Postgresql 8.3 and earlier I find the cast:
Source type Target type Function Implicit?
oid character_varying text in assignment
This cast is missing in Postgresql 8.4.
So, I thought I could resolve this by creating the appropriate cast.
After checking the documentation and running as the database user
postgres, I thought that the following CREATE CAST command would create
the "missing" oid to character varying cast:
CREATE CAST (oid AS character varying) WITH FUNCTION text(oid) AS
ASSIGNMENT;
However, this results in "ERROR: function text(oid) does not exist."
Clearly, I must have misread or misunderstood the CREATE CAST syntax in
my attempts to create a pre-Postgresql-8.4 cast from oid to character
varying. Does anyone with more experience using the CREATE CAST command
see my problem?
Thanks for your consideration,
John
