Using portable data types

Only a limited set of data types are really portable across several database engines.

The ANSI SQL specification defines standard data types, but for historical reasons most databases vendors have implemented native (non-standard) data types. You can usually use a synonym for ANSI types, but the database server will uses the native types behind the scenes. For example, when you create a table with an INTEGER column in Oracle, the native NUMBER data type is used.

In your programs, avoid data types that do not have a native equivalent in the target database. This includes simple types like floating point numbers, as well as complex data types like INTERVAL. Numbers may cause rounding or overflow problems, because the values stored in the database have different limits. For the DECIMAL types, always use the same precision and scale for the program variables and the database columns.

To write portable applications, we strongly recommend using the following data types only:
  • CHAR(n)
  • VARCHAR(n)
  • BIGINT
  • INTEGER
  • SMALLINT
  • DECIMAL(p,s)
  • DATE
  • DATETIME HOUR TO MINUTE
  • DATETIME HOUR TO SECOND
  • DATETIME HOUR TO FRACTION(n)
  • DATETIME YEAR TO MINUTE
  • DATETIME YEAR TO SECOND
  • DATETIME YEAR TO FRACTION(n)
Table 1. Data type differences in database engine brands
Database Server Type Data type topic
IBM® DB2® LUW (UNIX™) Data type conversion table: Informix to DB2
IBM Informix® Genero BDL is base on Informix SQL data types...
IBM Netezza® Data type conversion table: Informix to Netezza
Microsoft™ SQL Server Data type conversion table: Informix to SQL Server
Oracle® MySQL / MariadDB Data type conversion table: Informix to MySQL
Oracle Database Server Data type conversion table: Informix to Oracle
PostgreSQL Data type conversion table: Informix to PostgreSQL
SAP HANA® Data type conversion table: Informix to SAP HANA
SQLite Data type conversion table: Informix to SQLite