Search Postgresql Archives

Problems with "CREATE CAST"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux