Collection Data Types

This diagram shows the syntax to define a column or an SPL variable as a collection data type. (A table can include no more than 97 columns of collection data types.) For the syntax to specify values of collection elements, see Collection Constructors.
Collection Data Type

1 COLLECTION
1 
2.1 SET
2.1 MULTISET
2.1 LIST
1 (
2.1 data_type
2.1   (  data_type   NOT NULL )
1 NOT NULL)
Element Description Restrictions Syntax
data_type Data type of each of the elements of the collection Can be any data type except BIGSERIAL, BYTE, SERIAL, or SERIAL8, or TEXT Data Type

A SET is an unordered collection of elements, each of which has a unique value. Define a column as a SET data type when you want to store collections whose elements contain no duplicate values and have no associated order.

A MULTISET is an unordered collection of elements that can have duplicate values. You can define a column as a MULTISET collection type when you want to store collections whose elements might not be unique and have no specific order associated with them.

A LIST is an ordered collection of elements that can include duplicate elements. A LIST differs from a MULTISET in that each element in a LIST collection has an ordinal position in the collection. You can define a column as a LIST collection type when you want to store collections whose elements might not be unique but have a specific order associated with them.

The keyword COLLECTION can be used in SPL data type declarations to specify an untyped collection variable.

If you attempt to insert a collection that includes one or more duplicate values into a SET column, HCL OneDB™ issues no error, but the duplicate values are ignored, and only the unique values are inserted.