Collection Data Types

A collection data type is a complex type that is made up of one or more elements, all of the same data type. A collection element can be of any data type (including other complex types) except BYTE, TEXT, SERIAL, SERIAL8, or BIGSERIAL.

Important: An element cannot have a NULL value. You must specify the NOT NULL constraint for collection elements. No other constraints are valid for collections.

HCL Informix® supports three kinds of built-in collection types: LIST, SET, and MULTISET. The keywords used to declare these collections are the names of the type constructors or just constructors. For the syntax of collection types, see the Informix® Guide to SQL: Syntax. No more than 97 columns of the same table can be declared as collection data types.

When you specify element values for a collection, list the element values after the constructor and between braces ( { } ). For example, suppose you have a collection column with the following MULTISET data type: 
CREATE TABLE table1
(
   mset_col MULTISET(INTEGER NOT NULL)
)
The next INSERT statement adds one group of element values to this column. (The word MULTISET in these two examples is the MULTISET constructor.) 
INSERT INTO table1 VALUES (MULTISET{5, 9, 7, 5})
You can leave the braces empty to indicate an empty set: 
INSERT INTO table1 VALUE (MULTISET{})

An empty collection is not equivalent to a NULL value for the column.