MULTISET(e) data type

The MULTISET data type is a collection type that stores a non-ordered set that can include duplicate element values.

The elements in a MULTISET have no ordinal position. That is, there is no concept of a first, second, or third element in a MULTISET. (For a collection type with ordinal positions for elements, see LIST(e) data type.)

All elements in a MULTISET have the same element type. To specify the element type, use the following syntax: 
MULTISET(element_type NOT NULL)
The element_type of a collection can be any of the following types:
  • Any built-in type, except SERIAL, SERIAL8, BIGSERIAL, BYTE, and TEXT
  • An unnamed or a named ROW type
  • Another collection type or opaque type
You can use MULTISET anywhere that you use any other data type, unless otherwise indicated. For example:
  • After the IN predicate in the WHERE clause of a SELECT statement to search for matching MULTISET values
  • As an argument to the CARDINALITY or mi_collection_card( ) function to determine the number of elements in a MULTISET column

You cannot use MULTISET values as arguments to an aggregate function such as AVG, MAX, MIN, or SUM.

You must specify the NOT NULL constraint for MULTISET elements. No other constraints are valid for MULTISET columns. For more information about the MULTISET collection type, see the HCL OneDB™ Guide to SQL: Syntax.

Two multiset data values are equal if they have the same elements, even if the elements are in different positions within the set. The following examples are both multiset values but are not equal: 
MULTISET {"blue", "green", "yellow"}
MULTISET {"blue", "green", "yellow", "blue"}
The following multiset values are equal: 
MULTISET {"blue", "green", "blue", "yellow"}
MULTISET {"blue", "green", "yellow", "blue"}

No more than 97 columns of the same table can be declared as MULTISET data types. (The same restriction applies to SET and LIST collection types.)