LIST(e) (IDS)

The LIST data type is a collection type that stores ordered, non-unique elements; that is, it allows duplicate element values. The elements of a LIST have ordinal positions; that is, the list has a first element, a second element, and so on. (For a collection type with no ordinal positions, see MULTISET(e) (IDS) and SET(e) (IDS).)

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 LIST elements at the end of the list. 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 IBM Informix: Guide to SQL Syntax.

All elements in a LIST 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, 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 on the syntax of the LIST data type, see the IBM Informix: Guide to SQL Syntax.

You can use LIST 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.

Two list values are equal if they have the same elements in the same order. The following examples both are list values but are not equal:

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

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

The above statements are not equal because the values are not in the same order. To be equal, the second statement would have to be:

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