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:
- Using PostgreSQL JDBC override parameter (recommended).
- Using CREATE CAST to create a specific cast on the database.
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:
- Create a Properties file, adding the property detailed below.
- In the UI go to the Connector tab.
- 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:
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]);