LIST(e) data type

The LIST data type is a collection type that can store ordered non-NULL elements of the same SQL data type.

The LIST data type supports, but does not require, duplicate element values. The elements of a LIST data type have ordinal positions. The LIST object must have a first element, which can be followed by a second element, and so on.

For unordered collection data types that do not support ordinal positions, see MULTISET(e) data type and SET(e) data type. For complex data types that can store a set of values that includes different SQL data types, see ROW Data Types.

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

By default, the database server inserts new elements into a LIST object at the end of the set of elements. To support the ordinal position of a LIST, the INSERT statement provides the AT clause. This clause allows you to specify the position at which you want to insert a LIST element value. For more information, see the INSERT statement in the HCL OneDB™ Guide to SQL: Syntax.

All elements in a LIST object have the same element type. To specify the element type, use the following syntax:

LIST(element_type NOT NULL)

The element_type of a LIST can be any of the following data types:

A built-in type, except SERIAL, SERIAL8, BIGSERIAL, BYTE, and TEXT
A DISTINCT type
An unnamed or named ROW type
Another collection type
An opaque type

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

You can use LIST in most contexts where any other data type is valid. For example:

After the IN predicate in the WHERE clause of a SELECT statement to search for matching LIST values
As an argument to the CARDINALITY or mi_collection_card( ) function to determine the number of elements in a LIST column

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

Just as with the other collection data types, you must use parentheses ( ( ) ) in data type declarations to delimit the set of elements of a LIST data type:

CREATE FUNCTION  update_nums( list1 LIST (ROW (a VARCHAR(10),
                                               b VARCHAR(10),
                                               c INT) NOT NULL ));

In SQL expressions that include literal LIST values, however, you must use braces ( { } ) to delimit the set of elements of a LIST object, as in the examples that follow.

Two LIST values are equal if they have the same elements in the same order. The following are both examples of LIST objects, but their values are not equal. :

LIST{"blue", "green", "yellow"}

LIST{"yellow", "blue", "green"}

The above expressions are not equal because the values are not in the same order. To be equal, the second statement must be:

LIST{"blue", "green", "yellow"}