Type mapping for Decimal/Numeric type columns in PostgreSQL-based TIBCO Spotfire Connectors

Type mapping for Decimal/Numeric type columns in PostgreSQL-based TIBCO Spotfire Connectors

This articles describes the data type mappings in Spotfire when Decimal/Numeric type columns are used in TIBCO Spotfire Postgresql Connector and Postgresql based connectors like Redshift, HAWQ, and Greenplum connectors. The mapping for Numeric type columns in TIBCO Spotfire changes based on the precision being used.

The precision can be seen in the data type, like Numeric(p,s), where:

• p - Represents precision (total number of digits)
• s - Represents scale (number of digits after the decimal)

An example of Numeric(10,3), would be 1234567.123

The data type mapping for Numeric type columns in TIBCO Spotfire are defined as:

• When s = 0:
  • When p <= 9 then the mapping in Spotfire is Interger/Int
  • When 9 < p <= 18 then the mapping in Spotfire is LongInteger/Long
• When s >= 1: 
  • When p <= 15 then the mapping in Spotfire is Real/Double
  • When p > 15 then the mapping in Spotfire is Currency/Decimal

Examples:

• Numeric(10,5) maps to Real/Double in Spotfire
• Numeric(16,10) maps to Currency/Decimal in Spotfire
• Numeric(15,15) maps to Real/Double in Spotfire
• Numeric(30,5) maps to Currency/Decimal in Spotfire

In TIBCO Spotfire 7.5 and lower if scale is included, irrespective of precision, the column will be mapped to a Real in Spotfire.

This behavior was changed in TIBCO Spotfire 7.6 and later, because when there is a precision greater than fifteen, the number cannot completely be represented by a real (having a precision of fifteen). So in order not to loose significant digits the type is kept as decimal (which is Currency in the TIBCO Spotfire Analyst client) and the check for precision described above was introduced. Doc: PostgreSQL Data Types

• https://docs.tibco.com/pub/sfire-analyst/10.3.2/doc/html/en-US/TIB_sfire-analyst_UsersGuide/connectors/psql/psql_postgresql_data_types.htm