Syntax

Built-In Data Type

Universal Server supports the following categories of built-in data types:

• Character data types
• Numeric data types
• Large-object data types
• Time data types

In addition, Universal Server supports the BOOLEAN data type. For more information on the BOOLEAN data type, see Chapter 2 of the Informix Guide to SQL: Syntax and Chapter 9 of the Informix Guide to SQL: Tutorial.

Character Data Types

Element	Purpose	Restrictions	Syntax
max	Maximum size of a CHARACTER VARYING or VARCHAR or NVARCHAR column in bytes	You must specify an integer value between 1 and 255 bytes inclusive. If you place an index on the column, the largest value you can specify for max is 254 bytes.	Literal Number, p. 1-1001
size	Number of bytes in the CHAR or NCHAR column	You must specify an integer value between 1 and 32,767 bytes inclusive.	Literal Number, p. 1-1001
reserve	Amount of space in bytes reserved for a CHARACTER VARYING or VARCHAR or NVARCHAR column even if the actual number of bytes stored in the column is less than reserve	You must specify an integer value between 0 and 255 bytes. However, the value you specify for reserve must be less than the value you specify for max.	Literal Number, p. 1-1001

The following table summarizes the character data types that Universal Server supports.

Data Type	Purpose
CHAR	Stores single-byte or multibyte text strings of up to 32,767 bytes of text data and supports code-set collation of text data.
CHARACTER	Is an ANSI-compliant synonym for CHAR.
CHARACTER VARYING	Is a synonym for VARCHAR that complies with ANSI standards.
LVARCHAR NCHAR	Stores variable length strings that are potentially longer than 255 bytes. Store single-byte or multibyte text strings of up to 32,767 bytes of text data and supports localized collation of the text data
NVARCHAR	Stores single-byte or multibyte text strings of varying length and up to 255 bytes of text data; it supports localized collation of the text data.
VARCHAR	Stores single-byte or multibyte text strings of varying length and up to 255 bytes of text data; it supports code-set collation of the text data.

The TEXT and CLOB data types also support character data. For more information, see "Large-Object Data Types".

For more information on individual data types, see the description of the above data types in Chapter 2 of the Informix Guide to SQL: Syntax.

Fixed- and Varying- Length Data Types

A varying-length column requires only the number of bytes that its data uses. The VARCHAR and LVARCHAR data types are varying-length character data types. For example, a VARCHAR(25) column reserves up to 25 bytes of storage for the column value, but the string "This is a text string" uses only 21 bytes of the reserved 25 bytes.

The VARCHAR data type can store up to 255 bytes of varying data while the LVARCHAR data type can store up to 32 kilobytes of text data.

NCHAR and NVARCHAR Data Types

To accommodate locale-specific order of characters, use the NCHAR and NVARCHAR data types. The NCHAR data type is the fixed-length character data type the supports localized collation. The NVARCHAR data type is the varying-length character data type that can store up to 255 bytes of text data and supports localized collation.

For more information, see the Guide to GLS Functionality.

Numeric Data Types

• Exact numeric data types
• Approximate numeric data types

Element	Purpose	Restrictions	Syntax
precision	Total number of significant digits in a decimal or money data type	You must specify an integer between 1 and 32, inclusive.	Literal Number, p. 1-1001
scale	Number of digits to the right of the decimal point	You must specify an integer between 1 and precision.	Literal Number, p. 1-1001
start	Starting number for values in a SERIAL or SERIAL8 column	For SERIAL columns you must specify a number greater than 0 and less than 2,147,483,647. For SERIAL8 columns you must specify a number greater than 0 and less than 9,223,372,036,854,775,807.	Literal Number, p. 1-1001

An exact numeric data type stores a numeric value with a specified precision and scale. The precision of a number is the number of digits that the data type stores. The scale is the number of digits to the right of the decimal separator.

The following table summarizes the exact numeric data types that Universal Server supports.

(1 of 2)

Data Type	Purpose
DEC(p,s)	Is a synonym for DECIMAL(p,s).
DECIMAL(p,s)	Stores fixed-point decimal (real) values in the range. The p parameter indicates the precision of the decimal value and the s parameter indicates the scale. If no precision is specified, the system default of 16 is used. If no scale is specified, the system default of 0 is used.
INT	Is a synonym for INTEGER.
INTEGER	Stores a 4-byte integer value. These values can be in the range -((2**31)-1) to (2**31)-1 (the values -2,147,483,647 to 2,147,483,647).
INT8	Stores an 8-byte integer value. These values can be in the range -((2**63)-1) to (2**63)-1 (the values -9,223,372,036,854,775,807 to 9,223,372,036,854,775,807).
MONEY(p,s)	Stores fixed-point currency values. Has the same internal data type as a fixed-point DECIMAL value.
NUMERIC(p,s)	Is an ANSI-compliant synonym for DECIMAL(p,s).
SERIAL	Stores a 4-byte integer value that the database server generates. These values can be in the range -((2**31)-1) to (2**31)-1 (the values -2,147,483,647 to 2,147,483,647).
SERIAL8	Stores an 8-byte integer value that the database server generates. These values can be in the range -((2**63)-1) to (2**63)-1 (the values -9,223,372,036,854,775,807 to 9,223,372,036,854,775,807).
SMALLINT	Stores a 2-byte integer value. These values can be in the range -((2**15)-1) to (2**15)-1 (-32,767 to 32,767).

For more information, see the entries for these data types in Chapter 2 of the Informix Guide to SQL: Syntax.

Approximate Numeric Data Types

Element	Purpose	Restrictions	Syntax
float precision	The float precision is ignored.	You must specify a positive integer.	Literal Number, p. 1-1001

An approximate numeric data type represents numeric values approximately. Use them for very large and very small numbers that can tolerate some degree of rounding during arithmetic operations.

The following table summarizes the approximate numeric data types that Universal Server supports.

Data Type	Purpose
DOUBLE PRECISION	Is an ANSI-compliant synonym for FLOAT.
FLOAT	Stores double-precision floating-point numbers with up to 16 significant digits.
REAL	Is an ANSI-compliant synonym for SMALLFLOAT.
SMALLFLOAT	Stores single-precision floating-point numbers with approximately 8 significant digits.

For more information, see the entries for these data types in Chapter 2 of the Informix Guide to SQL: Syntax.

Large-Object Data Types

Element	Purpose	Restrictions	Syntax
blobspace	Name of an existing blobspace	The blobspace must exist.	Identifier, p. 1-966
family name	Quoted string constant that specifies a family name or variable name in the optical family	The family name or variable name must exist.	Quoted String, p. 1-1014 For additional information about optical families, see the INFORMIX-OnLine/Optical User Manual.

• Simple-large-object data types: TEXT and BYTE
• Smart-large-object data types: CLOB and BLOB

The following table summarizes the simple-large-object data types that Universal Server supports.

Data Type	Purpose
TEXT	Stores text data of up to 2**31 bytes.
BYTE	Stores text data of up to 2**31 bytes.

For more information, see the entries for these data types in Chapter 2 of the Informix Guide to SQL: Syntax.

Smart-Large-Object Data Types

The following table summarizes the smart-large-object data types that Universal Server supports.

Data Type	Purpose
CLOB	Stores text data of up to 4 terabytes (4*2 bytes).
BLOB	Stores binary data of up to 4 terabytes (4*2 bytes).

For more information, see the entries for these data types in Chapter 2 of Informix Guide to SQL: Syntax. For information about the SQL functions you use to import, export, and copy smart large objects, see "Smart-Large-Object Functions" in this manual and Chapter 9 of the Informix Guide to SQL: Tutorial.

Time Data Types

For more information, see the entries for these data types in Chapter 2 of the Informix Guide to SQL: Syntax.

User-Defined Data Type

Element	Purpose	Restrictions	Syntax
opaque data type	The name of the opaque data type	The name must be different from all other data types in the database.	Identifier, p. 1-966
distinct data type	The name of a distinct data type that has the same structure as an existing data type	The name must be different from all other data types in the database.	Identifier, p. 1-966
owner	The user name of the owner of the data type	If you are using an ANSI compliant database, you must enter the owner.type name to use a user-defined data type that you do not own. If you put quotation marks around the name you enter in owner, the named is stored exactly as typed. If you do not put quotation marks around the name that you enter in owner, the name is stored as uppercase letters.	The user name must conform to the conventions of your operating system.

• Opaque data types
• Distinct data types

Opaque Data Types

For more information, see the CREATE OPAQUE TYPE statement. For complete information about how to create an opaque type and its support functions, see the Extending INFORMIX-Universal Server: Data Types manual.

Distinct Data Types

Complex Data Type

Element	Purpose	Restrictions	Syntax
named row type name	The name of the named row type	The name must be different from all other data types in the database.	Identifier, p. 1-966 Data type, p. 1-859
owner	The user name of the owner of the data type	If you are using an ANSI-compliant database, you must enter the owner.type name to use a named row type that you do not own. If you put quotation marks around the name you enter in owner, the named is stored exactly as typed. If you do not put quotation marks around the name that you enter in owner, the name is stored as uppercase letters.	The user name must conform to the conventions of your operating system.

Complex data types are data types that you create from built-in types, opaque types, distinct types, or other complex types. When you create a complex type, you define the components of the complex type. However, unlike an opaque type, a complex type is not encapsulated. You can use SQL to access the individual components of a complex data type.

Universal Server supports the following categories of complex data types:

• Row types
  • Named row types
  • Unnamed row types
• Collection data types
  • SET
  • MULTISET
  • LIST

Named Row Types

For a complete description of named row types, see Chapter 10 of the Informix Guide to SQL: Tutorial and Chapter 2 of the Informix Guide to SQL: Syntax.

Unnamed Row Types

An unnamed row type is a group of fields that you create with the ROW constructor. You can use an unnamed row type to define a column. The syntax that you use to define a column as an unnamed row type is shown in the following diagram.

An unnamed row type is identified by its structure. For additional information about unnamed row types and how to create them, see Chapter 10 of the Informix Guide to SQL: Tutorial and Chapter 2 in the Informix Guide to SQL: Syntax.

For the syntax you use to specify row values for an unnamed row type, see the "Expression" segment on page 1-880.

Field Definition

Collection Data Types

A collection type contains elements that can be of a built-in type, an opaque type, a distinct type, or a row type. A collection type can also contain another collection type within it. You can use a collection type to define a column. The element type of a collection specifies the type of data that the collection can contain. For example, if the element type of a collection type is INTEGER, every element in the collection must be of type INTEGER. If the element type of a collection type is a row type, every element in the collection must be of the row type.

To create a collection data type, you must specify the following:

• A type constructor that establishes whether the collection is a SET, MULTISET, or LIST
• An element type that specifies the type of data that the collection can contain

For the syntax you use to specify collection values for a collection data type, see the "Literal DATETIME" segment on page 1-995.

SET Collection Types

MULTISET Collection Types

LIST Collection Types

References

In the Informix Guide to SQL: Tutorial, see the discussion of complex data types in Chapter 10.

In the Informix Guide to SQL: Syntax, see the discussions of individual data types in Chapter 2.

In the Guide to GLS Functionality, see the discussion of the NCHAR and NVARCHAR data types and the GLS aspects of other character data types.

DATETIME Field Qualifier

Syntax

Element	Purpose	Restrictions	Syntax
digit	A single integer that specifies the precision of a decimal fraction of a second. The default precision is 3 digits (a thousandth of a second).	You must specify an integer between 1 and 5, inclusive.	Literal Number, p. 1-1001

The following examples show DATETIME qualifiers:

DAY TO MINUTE



YEAR TO MINUTE



DAY TO FRACTION(4)



MONTH TO MONTH