Problems with "CREATE CAST"

Поиск
Список
Период
Сортировка
Искать
От
John Shott
Тема
Problems with "CREATE CAST"
Дата
Msg-id
4BA67193.7060201@stanford.edu
Список
Дерево обсуждения
Problems with "CREATE CAST" John Shott <shott@stanford.edu>
Re: Problems with "CREATE CAST" Tom Lane <tgl@sss.pgh.pa.us>
Re: Problems with "CREATE CAST" John Shott <shott@stanford.edu>
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



В списке pgsql-general по дате отправления
От: Tom Lane
Дата:
От: Vick Khera
Дата:
Сообщение: Re: Transaction table
FAQ