The 'citext' data type doesn't work in custom query option with Postgres connector connection
Article ID: KB0075534
Updated On:
| Products | Versions |
|---|---|
| Spotfire Connectors | All versions |
Description
'citext' data type if used inside the custom query option may throw the below error:
==========================
An error occurred in the external data source.
External error:
Unable to map the following columns:
– Column 'name' with data type 'unknown'
==========================
Note: While adding the data table directly(without custom query) after connecting to the Postgres database with the connector, the column with data type 'citext' is not visible inside the 'columns in view connection' window.
==========================
An error occurred in the external data source.
External error:
Unable to map the following columns:
– Column 'name' with data type 'unknown'
==========================
Note: While adding the data table directly(without custom query) after connecting to the Postgres database with the connector, the column with data type 'citext' is not visible inside the 'columns in view connection' window.
Issue/Introduction
The 'citext' data type doesn't work in custom query option with Postgres connector connection
Environment
All Supported OS
Resolution
Below are the available workarounds:
1) Make use of Lower() inside the custom query.
Example: Lower([Column Name])
Note: The above function will result in all the characters in lower case.
2) So if your data contains both, upper and lower case characters, then make use of CAST()
Example: CAST([Column Name] AS varchar)
1) Make use of Lower() inside the custom query.
Example: Lower([Column Name])
Note: The above function will result in all the characters in lower case.
2) So if your data contains both, upper and lower case characters, then make use of CAST()
Example: CAST([Column Name] AS varchar)
Additional Information
Feedback
Yes
No
Powered by
