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.