Troubleshooting PostgreSQL Type Cast Errors

This document describes Type Casts in PostgreSQL and how to avoid or fix casting errors.

Background Information

The term type cast refers to converting data types into another, so you have to change the data type of a column.
We can achieve this in PostgreSQL by creating the Type Cast. Type Casts can be used to solve errors caused by mixed data types, similar to this:

java.sql.SQLException: ERROR: operator does not exist: integer = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

As per the PostgreSQL official document Type Casts should be created with Superuser only and changing ownership for datatype should be avoided.

“To be able to create a cast, you must own the source or the target data type and have USAGE privilege on the other type. To create a binary-coercible cast, you must be superuser. (This restriction is made because an erroneous binary-coercible cast conversion can easily crash the server.)”

If you want to see the information in details, please follow the official PostgreSQL official document.

How is that related to censhare?

In censhare, type casts can be found in the script "censhare-Server/database/postgresql-create-user.sql".

Type cast errors on new censhare Server installation

If you see the error "ERROR: must be owner of type character varying or type integer" on a fresh censhare Server installation, simply execute the script using the command below to apply them correctly:

Hints: In the below the database schema is named corpus.
Please adapt accordingly and make sure to use the DB superuser with admin permissions as postgres.

psql -U postgres -d corpus -f {PATH_TO}/censhare-Server/database/postgresql-create-user.sql

Hint: Even though the script might terminate with an error when executed on an already existing database, all type casts will be re-applied properly.

Type cast error on existing censhare Server installation

It can happen that you get an error message like this:

Client Version: 2017.5.4, 28.02.2018
Login Name: pe
Host URL: frmis://cs-test20175-css01/corpus.RMIServerSSL
Server Name: master
Server Version: 2017.5.4
Build Number: 5668
Server DB Version: 2017.5.0.0004
Java Version: 1.8.0_152
VFS: Disabled
Operating System: Mac OS X, 10.13.3, x86_64

The following error occurred on Mar 1, 2018, 2:50:07 PM.
com.censhare.server.rmi.RMIServerException[java.lang.RuntimeException]: java.sql.SQLException: ERROR: operator does not exist: integer = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

If you see the above error on an existing censhare Server installation, you should execute the Type Casts from within " censhare-Server/database/postgresql-create-user.sql " like below on censhare application server.

Find all Type Casts

grep -i "CREATE CAST" ~/censhare/censhare-Server/database/postgresql-create-user.sql 
CREATE CAST (varchar AS integer) WITH INOUT AS IMPLICIT;
CREATE CAST (bigint AS varchar) WITH INOUT AS IMPLICIT;

Apply all Type Casts

Hints: In the below the database schema is named corpus.
Please adapt accordingly and make sure to use the DB superuser with admin permissions as postgres.

ssh postgres@database-server
psql -U postgres -d corpus

CREATE CAST (varchar AS integer) WITH INOUT AS IMPLICIT;
CREATE CAST (bigint AS varchar) WITH INOUT AS IMPLICIT;

 \q

Hint: Even though this might terminate with an error when executed on an already existing database, all type casts will be re-applied properly.

General error message while applying Type Casts

Sometimes, the user is getting the error as below during creating PostgreSQL Type Casts from postgres user:

psql -U corpus

\c testdatabase
You are now connected to database "testdatabase" as user "corpus".
postgres=>

CREATE CAST (varchar AS integer) WITH INOUT AS IMPLICIT;
ERROR: must be owner of type character varying or type integer
CREATE CAST (bigint AS varchar) WITH INOUT AS IMPLICIT;
ERROR: must be owner of type bigint or type character varying

Hint: In the above example, the database name is “testdatabase" and user “corpus”.

As we described above that it is advisable cast should be created by the Superuser only. In above case, the user trying to create the Type Cast with the normal user (in this case, user is corpus=>) which have no Superuser rights.

Question: How can we make sure that the user connected to PostgreSQL database through the normal user or Superuser?
Answer: If you see the symbol like this (=>) as above, it means that user is connected to PostgreSQL by normal user and that’s why the user got the above error. If you see the symbol like this (=#) as below, it means that user is connected to PostgreSQL by Superuser and is able to create the Type Cast, see below as an example after connecting to psql by postgres user (Yourhost:~ postgres$):

psql

postgres=# \c testdatabase
You are now connected to database "testdatabase" as user "postgres".

CREATE CAST (varchar AS integer) WITH INOUT AS IMPLICIT;
CREATE CAST
CREATE CAST (bigint AS varchar) WITH INOUT AS IMPLICIT;
CREATE CAST

Related articles