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 Only).
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, SQL statements that create specific data types and create and drop casts, and other data type topics, refer to the IBM Informix: Guide to SQL Syntax.
For information about how to create and use complex data types, 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 1 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 in same range as INT | SERIAL(n ) |
| SERIAL8 | Stores sequential integers in same range as 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 2 lists.
| Data Type | Explanation | Page |
|---|---|---|
| BLOB | Stores binary data in random-access chunks | BLOB (IDS Only) |
| BOOLEAN | Stores Boolean values true and false | BOOLEAN (IDS Only) |
| CLOB | Stores text data in random-access chunks | CLOB (IDS Only) |
| 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 Only) |
| LIST(e) | Stores a sequentially ordered collection of elements, all of the same data type, e; allows duplicate values | LIST(e) (IDS Only) |
| LVARCHAR(m) | Stores variable-length strings of up to 32,739 bytes | LVARCHAR(m) (IDS Only) |
| MULTISET(e) | Stores a non-ordered collection of values, with elements all of the same data type, e; allows duplicate values. | MULTISET(e) (IDS Only) |
| Opaque | Stores a user-defined data type whose internal structure is inaccessible to the database server | Opaque (IDS Only) |
| ROW, Named | Stores a named ROW type | ROW, Named (IDS Only) |
| ROW, Unnamed | Stores an unnamed ROW type | ROW, Unnamed (IDS Only) |
| SET(e) | Stores a non-ordered collection of elements, all of the same data type, e; does not allow duplicate values | SET(e) (IDS Only) |
Distributed queries that access remote tables cannot retrieve these extended data types, which are individually described in this chapter.
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 ]