NUMERIC data types

Informix®

Informix supports several data types to store numbers:

Table 1. Informix numeric data types
Informix data type Description
SMALLINT 16 bit signed integer
INTEGER 32 bit signed integer
BIGINT 64 bit signed integer
INT8 64 bit signed integer (replaced by BIGINT)
DECIMAL Equivalent to DECIMAL(16)
DECIMAL(p) Floating-point decimal number (max precision is 32)
DECIMAL(p,s) Fixed-point decimal number (max precision is 32)
MONEY Equivalent to DECIMAL(16,2)
MONEY(p) Equivalent to DECIMAL(p,2) (max precision is 32)
MONEY(p,s) Equivalent to DECIMAL(p,s) (max precision is 32)
REAL / SMALLFLOAT 32-bit floating point decimal (C float)
DOUBLE PRECISION / FLOAT[(n)] 64-bit floating point decimal (C double)

PostgreSQL

PostgreSQL supports the following data types to store numbers:

Table 2. PostgreSQL numeric data types
PostgreSQL data type Description
INT2 / SMALLINT 16 bit signed integer
INT4 / INTEGER 32 bit signed integer
INT8 / BIGINT 64 bit signed integer
DECIMAL(p,s) Decimals with precision and scale (fractional part)
DECIMAL(p) Integers with p digits (no fractional part)
DECIMAL Floating point numbers (no limit)
FLOAT4 16 bit variable precision
FLOAT8 32 bit variable precision

ANSI types like SMALLINT, INTEGER, FLOAT are supported by PostgreSQL as aliases to INT2, INT4 and FLOAT8 native types.

Informix DECIMAL(p) floating point types are converted to DECIMAL without precision/scale, to store any floating point number in PostgreSQL.

Solution

Use the following conversion rules to map Informix numeric types to PostgreSQL numeric types:

Table 3. Informix numeric data types and PostgreSQL equivalents
Informix data type PostgreSQL equivalent
SMALLINT INT2
INTEGER INT4
INT8 / BIGINT INT8
DECIMAL(p,s) DECIMAL(p,s)
DECIMAL(p) DECIMAL
DECIMAL DECIMAL
MONEY(p,s) DECIMAL(p,s)
MONEY(p) DECIMAL(p,2)
MONEY DECIMAL(16,2)
SMALLFLOAT FLOAT4
FLOAT[(n)] FLOAT8
The numeric types translation can be controlled with the following FGLPROFILE entries:
dbi.database.dsname.ifxemul.datatype.smallint = { true | false }
dbi.database.dsname.ifxemul.datatype.integer = { true | false }
dbi.database.dsname.ifxemul.datatype.bigint = { true | false }
dbi.database.dsname.ifxemul.datatype.int8 = { true | false }
dbi.database.dsname.ifxemul.datatype.decimal = { true | false }
dbi.database.dsname.ifxemul.datatype.money = { true | false }
dbi.database.dsname.ifxemul.datatype.float = { true | false }
dbi.database.dsname.ifxemul.datatype.smallfloat = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.