SQL adaptation guide For SQLite 3.5.x, 3.6.x / Data dictionary |
Informix® supports several data types to store numbers:
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:
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) |
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.