Collection Data Types

This diagram shows the syntax to define a column or of 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:
 
|--+-COLLECTION---------------------------------------------------------------+--|
   '-+-SET------+--(--+-data_type--------------------------------+--NOT NULL)-'
     +-MULTISET-+     '-+-SET------+--(--data_type-- NOT NULL--)-'
     '-LIST-----'       +-MULTISET-+
                        '-LIST-----'
 

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 in which elements 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.

Defining the Element Type

The element type can be any data type except TEXT, BYTE, SERIAL, or SERIAL8. You can nest collection types, using elements of a collection type.

Every element must be of the same type. For example, if the element type of a collection data type is INTEGER, every element must be of type INTEGER.

An exception to this restriction occurs if the database server determines that some elements of a collection of character strings are VARCHAR data types (whose length is limited to 255 or fewer bytes) but other elements are longer than 255 bytes. In this case, the collection constructor can assign a CHAR(n) data type to all elements, for n the length in bytes of the longest element. If this is undesirable, you can cast the collection to LVARCHAR, to prevent padding extra length in elements of the collection, as in this example:

LIST {'first character string longer than 255 bytes . . . ',
   'second character string longer than 255 bytes . . . ',
   'another character string'} ::LIST (LVARCHAR NOT NULL)

See Collection Constructors for additional information.

If the element type of a collection is an unnamed ROW type, the unnamed ROW type cannot contain fields that hold unnamed ROW types. That is, a collection cannot contain nested unnamed ROW data types.

The elements of a collection cannot be NULL. When you define a column as a collection data type, you must use the NOT NULL keywords to specify that the elements of the collection cannot be NULL.

Privileges on a collection data type are those of the database column. You cannot specify privileges on individual elements of a collection.

Related Information