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 OneDB™ 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 HCL OneDB 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.