Skip to main content

Resolving Varchar Cast Errors on PostgreSQL (KBA6993)




KBA# 6993


At a Glance

Applicable Versions: Applicable to all Delphix versions. Workaround detailed for
Description: The data types in PostgreSQL are strictly defined and casts between text and non-text data types are not automatic. To resolve this, use a JDBC override parameter or specifically make a cast on the database for this data type conversion. 
Database applicable to: PostgreSQL

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases





Error Message

This article addresses data type conversion errors similar to: 

[xyz]... was aborted: ERROR: column "[field]" is of type [type] but expression is of type character varying
Hint: You will need to rewrite or cast the expression.

Another example of this type of error message is: 

[xyz]... was aborted: ERROR: operator does not exist: [type] = character varying
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

The error message suggests that the data type of the field used in the masking operation is not able to be converted to a variable character field (or varchar).


There are two possible solutions to this issue:

  1. Using PostgreSQL JDBC override parameter (recommended).
  2. Using CREATE CAST to create a specific cast on the database.


CREATE CAST can have side effects, so the recommended option is to use the JDBC override. 

Option 1 - JDBC override parameter

The best option is to use the JDBC override parameter setting in the Masking Connector.

The following steps work for version 6.0.6 and higher:

  1. Create a Properties file, adding the property detailed below.
  2. In the UI go to the Connector tab.
  3. Open the PostgreSQL connector and click 'Select' and add the properties file.

For details on how to add a Properties file, see link in the Related Articles section below. 


Create a file with the .properties extension and then add the following string to that file:



Version 6.0.5 and older

The properties file is not available on 6.0.5 and older versions. Instead, use a General Connector and set the JDBC as follows: 



Custom Driver Name



Option 2 - CREATE CAST

Should the method in option 1 not work, then add the following to the database: 



Please verify the data type with the issue and set the [type] below.


CREATE CAST (varchar AS [type]) WITH inout AS IMPLICIT;

Since the cast above is setting this on a database level, it might conflict when the type needs to have the default setting and might have unexpected side effects. It is, therefore, recommended to use Option 1. 


Should the CAST need to be dropped use the following statement: 

DROP CAST (varchar AS [type]);


Related Articles