Skip to main content
Delphix

Resolving Varchar Cast Errors on PostgreSQL (KBA6993)

 

KBA

KBA# 6993

 

At a Glance

Applicable Versions: Applicable to all Delphix versions. Workaround detailed for 6.0.6.0+.
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
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

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).

Resolution

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.
Note

Note:

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:

stringtype=unspecified

 

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: 

JDBC URL

jdbc:postgresql://host:port/database?stringtype=unspecified

Custom Driver Name

org.postgresql.Driver

 

Option 2 - CREATE CAST

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

Note

Note:

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