Summary of data types
IBM® Informix® supports the most common set of built-in data types. Additionally, an extended set of data types are supported on the database server.
- Use them to create columns within database tables.
- Declare them as arguments and as returned types of routines.
- Use them as base types from which to create DISTINCT data types.
- Cast them to other data types.
- Declare and access host variables of these types in SPL and ESQL/C.
You assign data types to columns with the CREATE TABLE statement and change them with the ALTER TABLE statement. When you change an existing column data type, all data is converted to the new data type, if possible.
For information about the ALTER TABLE and CREATE TABLE statements, on SQL statements that create specific data types, that create and drop casts, and on other data type topics, see the IBM Informix Guide to SQL: Syntax.
For information about how to create and use complex data types supported by IBM Informix, see the IBM Informix Database Design and Implementation Guide. For information about how to create user-defined data types, see IBM Informix User-Defined Routines and Data Types Developer's Guide.
Some data types can be used in distributed SQL operations, while others can be used only in SQL operations within the same database.
Built-in data types supported in local and distributed SQL operations
- Operations on objects in the local database
- Cross-database operations on objects in databases of the local server instance
- Cross-server operations on objects in databases of two or more database server instances
| Data type | Explanation |
|---|---|
| BIGINT data type | Stores 8-byte integer values from -(263 -1) to 263 -1 |
| BIGSERIAL data type | Stores sequential, 8-byte integers from 1 to 263 -1 |
| BSON and JSON built-in opaque data types | The BSON data type is the binary representation of a JSON data type format for serializing JSON documents. The JSON data type is a plain text format for entering and displaying structured data. |
| BYTE data type | Stores any kind of binary data, up to 231 bytes in length |
| CHAR(n) data type | Stores character strings; collation is in code-set order |
| CHARACTER(n) data type | Is a synonym for CHAR |
| CHARACTER VARYING(m,r) data type | Stores character strings of varying length (ANSI-compliant); collation is in code-set order |
| DATE data type | Stores calendar dates |
| DATETIME data type | Stores calendar date combined with time of day |
| DEC data type | Is a synonym for DECIMAL |
| DECIMAL | Stores floating-point numbers with definable precision; if database is ANSI-compliant, the scale is zero |
| DECIMAL (p,s) Fixed Point | Stores fixed-point numbers of defined scale and precision |
| DOUBLE PRECISION data types | Synonym for FLOAT |
| FLOAT(n) | Stores double-precision floating-point numbers corresponding to the double data type in C |
| INT data type | Is a synonym for INTEGER |
| INT8 | Stores 8-byte integer values from -(263 -1) to 263 -1 |
| INTEGER data type | Stores whole numbers from -2,147,483,647 to +2,147,483,647 |
| INTERVAL data type | Stores a span of time (or level of effort) in units of years and months. |
| INTERVAL data type | Stores a span of time in a contiguous set of units of days, hours, minutes, seconds, and fractions of a second |
| MONEY(p,s) data type | Stores currency amounts |
| NCHAR(n) data type | Same as CHAR, but can support localized collation |
| NUMERIC(p,s) data type | Synonym for DECIMAL(p,s) |
| NVARCHAR(m,r) data type | Same as VARCHAR, but can support localized collation |
| REAL data type | Is a synonym for SMALLFLOAT |
| SERIAL(n) data type | Stores sequential integers ( > 0) in positive range of INT |
| SERIAL8(n) data type | Stores sequential integers ( > 0) in positive range of INT8 |
| SMALLFLOAT | Stores single-precision floating-point numbers corresponding to the float data type of the C language |
| SMALLINT data type | Stores whole numbers from -32,767 to +32,767 |
| TEXT data type | Stores any kind of text data, up to 231 bytes in length |
| VARCHAR(m,r) data type | Stores character strings of varying length (up to 255 bytes); collation is in code-set order |
In cross-server MERGE operations, the source table (but not the target table) can be in a database of a remote Informix server.
For the character data types (CHAR, CHAR VARYING, LVARCHAR, NCHAR, NVARCHAR, and VARCHAR), a data string can include letters, digits, punctuation, whitespace, diacritical marks, ligatures, and other printable symbols from the code set of the database locale. For UTF-8 and for code sets of some East Asian locales, multibyte characters are supported within data strings.
Built-in data types supported only in local database SQL operations
| Data type | Explanation |
|---|---|
| BLOB data type | Stores binary data in random-access chunks |
| The binary18 data type | Stores 18 byte binary-encoded strings |
| The binaryvar data type | Stores binary-encoded strings with a maximum length of 255 bytes |
| BOOLEAN data type | Stores Boolean values true and false |
| CLOB data type | Stores text data in random-access chunks |
| DISTINCT data types | Stores data in a user-defined type that has the same format as a source type on which it is based, but its casts and functions can differ from those on the source type |
| Calendar data type | Stores a calendar for a TimeSeries data type |
| CalendarPattern data type | Stores the structure of the calendar pattern for a Calendar data type |
| IDSSECURITYLABEL data type | Stores LBAC security label objects. |
| LIST(e) data type | Stores a sequentially ordered collection of elements, all of the same data type, e; allows duplicate values |
| The lld_locator data type | Stores a large object identifier |
| The lld_lob data type | Stores the location of a smart large object and specifies whether the object contains binary or character data |
| LVARCHAR(m) data type | Stores variable-length strings of up to 32,739 bytes |
| MULTISET(e) data type | Stores a non-ordered collection of values, with elements all of the same data type, e; allows duplicate values. |
| The node data type for querying hierarchical data | Stores a combination of integers and decimal points that represents hierarchical relationships, of variable length up to 256 characters |
| OPAQUE data types | Stores a user-defined data type whose internal structure is inaccessible to the database server |
| ROW data type, Named | Stores a named ROW type |
| ROW data type, Unnamed | Stores an unnamed ROW type |
| SET(e) data type | Stores a non-ordered collection of elements, all of the same data type, e; does not allow duplicate values |
| ST_LineString data type | Stores a one-dimensional object as a sequence of points defining a linear interpolated path |
| ST_MultiLineString data type | Stores a collection of ST_LineString data types |
| ST_MultiPoint data type | Stores a collection of ST_Point data types |
| ST_MultiPolygon data type | Stores a collection of ST_Polygon data types |
| ST_Point data type | Stores a zero-dimensional geometry that occupies a single location in coordinate space |
| ST_Polygon data type | Stores a two-dimensional surface stored as a sequence of points defining its exterior bounding ring and 0 or more interior rings |
| TimeSeries data type | Stores a collection of row subtypes |
These extended data types of Informix are individually described in other topics. These data types are valid in local operations on databases where the data types are defined.
Extended data types in cross-database distributed SQL transactions
Distributed operations on other databases of the same Informix instance can access BOOLEAN, BLOB, CLOB, and LVARCHAR data types, which are implemented as built-in opaque types. Such operations can also access DISTINCT types whose base types are built-in types, and user-defined types (UDTs), if the UDTs and DISTINCT types are explicitly cast to built-in types, and if all of the UDTs, casts, and DISTINCT types are defined in all the participating databases.
- UDTs that are not cast to built-in data types
- DISTINCT types that are not cast to built-in data types
- Collection data types
- Named or unnamed ROW data types
Extended data types in cross-server distributed SQL transactions
- Any non-opaque built-in data type
- BOOLEAN
- DISTINCT of non-opaque built-in types
- DISTINCT of BOOLEAN
- DISTINCT of LVARCHAR
- DISTINCT of any of the DISTINCT types listed above
- IDSSECURITYLABEL
- LVARCHAR
A cross-server distributed SQL transaction can support DISTINCT data types only if they are cast explicitly to built-in types, and all of the DISTINCT types, their data type hierarchies, and their casts are defined exactly the same way in each database that participates in the distributed operation. For queries or other DML operations in cross-server UDRs that use the data types in the preceding list as parameters or as returned data types, the UDR must also have the same definition in every participating database.
The built-in DISTINCT data type IDSSECURITYLABEL, which stores security label objects, can be accessed in cross-server and cross-database operations on protected data by users who hold sufficient security credentials. Like local operations on protected data, distributed queries that access remote tables protected by a security policy can return only the qualifying rows that IDSLBACRULES allow, after the database server has compared the security label that secures the data with the security credentials of the user who issues the query.