informix
Informix Guide to SQL: Reference
Data Types

Summary of Data Types

Figure 2-1 charts the categories of data types that Informix database servers support.

Figure 2-1 1Overview of Supported Data Types

Built-in data types and extended data types share the following characteristics. You can:

Specific exceptions are mentioned in the description of each data type. For an overview, see Built-In Data Types and Extended Data Types.

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 syntax conventions, refer to the Informix Guide to SQL: Syntax.

For information about how to create and use complex data types, see the Informix Guide to Database Design and Implementation.

For information about how to create user-defined data types, see Extending Informix Dynamic Server 2000.

All Informix database servers support the data types that Figure 2-2 lists. This chapter describes each of these data types.

Figure 2-2
Data Types That All Informix Database Servers Support

Data Type Explanation Page
BYTE Stores any kind of binary data 2-12
CHAR(n) Stores single-byte or multibyte sequences of characters, including letters, numbers, and symbols; collation is code-set dependent 2-13
CHARACTER(n) Is a synonym for CHAR 2-14
CHARACTER VARYING(m,r) Stores single-byte and multibyte sequences of characters, including letters, numbers, and symbols of varying length (ANSI compliant); collation is code-set dependent 2-15
DATE Stores calendar date 2-16
DATETIME Stores calendar date combined with time of day 2-17
DEC Is a synonym for DECIMAL 2-21
DECIMAL Stores numbers with definable scale and precision 2-21
DOUBLE PRECISION Behaves the same way as FLOAT 2-24
FLOAT(n) Stores double-precision floating-point numbers corresponding to the double data type in C 2-24
INT Is a synonym for INTEGER 2-25
INTEGER Stores whole numbers from -2,147,483,647 to +2,147,483,647 2-26
INTERVAL Stores a span of time 2-26
MONEY(p,s) Stores currency amount 2-32
MULTISET(e) Stores a collection (all elements of same element type, e) of values; allows duplicate values. 2-33
NCHAR(n) Stores single-byte and multibyte sequences of characters, including letters, numbers, and symbols; collation is locale dependent 2-34
NUMERIC(p,s) Is a synonym for DECIMAL 2-34
NVARCHAR(m,r) Stores single-byte and multibyte sequences of characters, including letters, numbers, and symbols of varying length; collation is locale dependent 2-34
REAL Is a synonym for SMALLFLOAT 2-35
Row, Named Stores a named row type 2-36
SERIAL Stores sequential integers 2-39
SERIAL8 Stores large sequential integers; has same range as INT8 2-40
SMALLFLOAT Stores single-precision floating-point numbers corresponding to the float data type in C 2-43
SMALLINT Stores whole numbers from -32,767 to +32,767 2-44
TEXT Stores any kind of text data. 2-44
VARCHAR(m,r) Stores single-byte and multibyte strings of letters, numbers, and symbols of varying length; collation is code-set dependent. 2-46

Dynamic Server also supports the data types that Figure 2-3 lists. This chapter describes each of these data types.

Figure 2-3
Additional Data Types that Dynamic Server Support

Data Type Explanation Page
BLOB Stores binary data in random-access chunks 2-9
BOOLEAN Stores Boolean values true and false 2-11
CLOB Stores text data in random-access chunks 2-15
Distinct Is a user-defined data type that is stored in the same way as the source data type on which it is based but has different casts and functions defined over it than those on the source type 2-23
INT8 Stores an 8-byte integer value. These whole numbers can be in the range -(263 -1) to 263 -1. 2-25
LIST(e) Stores a collection of elements of the same element type, e; elements have an implicit order (first, second, and so on); allows duplicate values 2-30
LVARCHAR Stores variable-length data that can be larger than 255 bytes 2-31
MULTISET(e) Stores a collection of elements of the same element type, e; allows duplicate values. 2-33
Opaque Stores a user-defined data type whose internal structure is inaccessible to the database server 2-35
Row, Named Stores a named row type 2-36
Row, Unnamed Stores an unnamed row type 2-37
SERIAL8 Stores large sequential integers; has same range as INT8 2-40
SET(e) Stores a collection of elements of the same element type, e; does not allow duplicate values 2-42
s

For information about Informix internal data types that SQL statements support (IMPEX, IMPEXBIN, and SENDRECV), see Extending Informix Dynamic Server 2000.

Informix Guide to SQL: Reference, Version 9.2
Copyright © 1999, Informix Software, Inc. All rights reserved