NUMERIC data types

Informix® supports several data types to store numbers:

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

SQLite 3 supports INTEGER (8 byte integer) and REAL (8 byte floating point) as native types to store numbers, but allows synonyms:

Table 2. SQLite numeric data types and supported synonyms
Supported synonyms SQLite type affinity
INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8 INTEGER (8 bytes!)
REAL, DOUBLE, DOUBLE PRECISION, FLOAT REAL (8 bytes!)
DECIMAL(p,s), NUMERIC NUMERIC (based on REAL)
Important: Exact decimal types like DECIMAL(p,s) may be stored as floating point numbers (REAL), INTEGERs or TEXT, according to the type affinity selected by SQLite. When converted to floating point type, data loss and rounding rule differences are possible with SQLite.

Solution

Informix numeric types are not translated by the database driver.

Since SQLite 3 does not have exact decimal types like DECIMAL(p,s), you must pay attention to the rounding rules and data loss when using numbers with many significant digits. Arithmetic operations like division have different results than with Informix. It is better to fetch the original column value into a DECIMAL variable, and do arithmetic operations in the application program.