Figure 1 shows the logical categories of data types that Informix database servers support. Shaded categories are for Dynamic Server only.
This diagram is simplified; some built-in types are implemented as opaque types, and are only supported on Dynamic Server. That is, opaque and built-in are not disjunct categories, though most built-in data types are not opaque.
Built-in data types (which are system-defined) and extended data types (which you can define) share the following characteristics. You can:
For exceptions, see the description of each data type. For an overview, see Built-In Data Types and Extended Data Types (IDS).
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 on 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, refer to the IBM Informix: Guide to SQL Syntax.
For information about how to create and use complex data types of Dynamic Server, 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.
All Informix database servers support the data types that Table 4 lists. This chapter describes each of these built-in data types.
| Data Type | Explanation | Page |
|---|---|---|
| BIGINT | Is a synonym (in XPS only) for INT8 | INT8 |
| BYTE | Stores any kind of binary data, up to 231 bytes in length | BYTE |
| CHAR(n) | Stores character strings; collation is in code-set order | CHAR(n) |
| CHARACTER(n) | Is a synonym for CHAR | CHARACTER(n) |
| CHARACTER VARYING(m,r) | Stores character strings of varying length
(ANSI compliant); collation is in code-set order |
CHARACTER VARYING(m,r) |
| DATE | Stores calendar dates | DATE |
| DATETIME | Stores calendar date combined with time of day | DATETIME |
| DEC | Is a synonym for DECIMAL | DEC |
| DECIMAL(p) | Stores floating-point numbers with definable precision; if database is ANSI-compliant, the scale is zero. | DECIMAL |
| DECIMAL(p, s) | Stores fixed-point numbers of defined scale and precision | DECIMAL (p,s) Fixed Point |
| DOUBLE PRECISION | Synonym for FLOAT | DOUBLE PRECISION |
| FLOAT(n) | Stores double-precision floating-point numbers corresponding to the double data type in C | FLOAT(n) |
| INT | Is a synonym for INTEGER | INT |
| INT8 | Stores 8-byte integer values in range -(263 -1) to 263 -1 | INT8 |
| INTEGER | Stores whole numbers in a range from -2,147,483,647 to +2,147,483,647 | INTEGER |
| INTERVAL (Year|Month) | Stores a span of time (or level of effort) in units of years and months. | INTERVAL |
| INTERVAL (Day|Fraction) | Stores a span of time in a contiguous set of units of days, hours, minutes, seconds, and fractions of a second. | INTERVAL |
| MONEY(p,s) | Stores currency amounts | MONEY(p,s) |
| NCHAR(n) | Same as CHAR, but can support localized collation | NCHAR(n) |
| NUMERIC(p,s) | Synonym for DECIMAL(p,s) | NUMERIC(p,s) |
| NVARCHAR(m,r) | Same as VARCHAR, but can support localized collation | NVARCHAR(m,r) |
| REAL | Is a synonym for SMALLFLOAT | REAL |
| SERIAL | Stores sequential integers ( > 0) in positive range of INT | SERIAL(n) |
| SERIAL8 | Stores sequential integers ( > 0) in positive range of INT8 | SERIAL8(n) |
| SMALLFLOAT | Stores single-precision floating-point numbers corresponding to the float data type of the C language | SMALLFLOAT |
| SMALLINT | Stores whole numbers from -32,767 to +32,767 | SMALLINT |
| TEXT | Stores any kind of text data, up to 231 bytes in length | TEXT |
| VARCHAR(m,r) | Stores character strings of varying length (up to 255 bytes); collation is in code-set order. | VARCHAR(m,r) |
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 some East Asian locales, multibyte characters are supported within data strings.)
Dynamic Server also supports additional data types that Table 5 lists.
| Data Type | Explanation | Page |
|---|---|---|
| BLOB | Stores binary data in random-access chunks | BLOB (IDS) |
| BOOLEAN | Stores Boolean values true and false | BOOLEAN (IDS) |
| CLOB | Stores text data in random-access chunks | CLOB (IDS) |
| Distinct | 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 | Distinct (IDS) |
| LIST(e) | Stores a sequentially ordered collection of elements, all of the same data type, e; allows duplicate values | LIST(e) (IDS) |
| LVARCHAR(m) | Stores variable-length strings of up to 32,739 bytes | LVARCHAR(m) (IDS) |
| MULTISET(e) | Stores a non-ordered collection of values, with elements all of the same data type, e; allows duplicate values. | MULTISET(e) (IDS) |
| Opaque | Stores a user-defined data type whose internal structure is inaccessible to the database server | Opaque (IDS) |
| ROW, Named | Stores a named ROW type | ROW, Named (IDS) |
| ROW, Unnamed | Stores an unnamed ROW type | ROW, Unnamed (IDS) |
| SET(e) | Stores a non-ordered collection of elements, all of the same data type, e; does not allow duplicate values | SET(e) (IDS) |
Distributed DML operations and function calls that access databases of other database servers cannot return these extended data types of Dynamic Server, which are individually described in this chapter. (Cross-database operations on other databases of the same Dynamic Server instance, however, 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.)
For information about Informix internal data types that SQL statements support (such as IMPEX, IMPEXBIN, and SENDRECV), see IBM Informix: User-Defined Routines and Data Types Developer's Guide.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]