Can TIBCO Spotfire read "Serial" Data Type from a PostgreSQL data table?

Can TIBCO Spotfire read "Serial" Data Type from a PostgreSQL data table?

book

Article ID: KB0080253

calendar_today

Updated On:

Products Versions
Spotfire Connector for PostgreSQL All versions

Description

In PostgreSQL, the data types 'smallserial', 'serial' and 'bigserial' are not true data types, but merely a notational convenience for creating unique identifier columns. When a custom query (in Spotfire connector) is used to retrieve data from a PostgreSQL data table that contains column type(s) as serial, Spotfire throws a query verification error as below:

External error:
Unable to map the following columns:
 – Column <column name> with data type 'serial'

Issue/Introduction

Can TIBCO Spotfire read "Serial" Data Type from a PostgreSQL data table?

Environment

All supported OS versions

Resolution

This is a product limitation for Spotfire versions 7.13 and below. This can be handled using the below workarounds:

- Using the data table directly instead of writing a custom query.
- Changing the data type of 'Serial' to 'Integer' in the database itself.
- Casting the 'serial' column type to 'varchar'. Inside the custom query dialog box, you can write a type-casted select query like: "select CAST (serial_col AS VARCHAR) from table".

Note: This limitation is addressed in Spotfire version 7.14 and higher. The mapping of serial data types in Spotfire is as follows:
 
PostgreSQL data typeSpotfire data type
serialInteger
smallserialInteger
bigserialLong

Additional Information

https://www.postgresql.org/docs/9.5/static/datatype-numeric.html#DATATYPE-SERIAL
https://docs.tibco.com/pub/spc/4.0.0/doc/html/psql/psql_postgresql_data_types.htm