Array types and values

Array values

An array value is a structure that contains an ordered collection of elements. All elements of an array value must have the same data type. The cardinality of the array is equal to the number of elements in the array.

An array value can be non-empty, empty (cardinality zero), or null. The individual elements in the array can be null or not null. An empty array, an array value of null, and an array for which all elements are the null value are different from each other. An uninitialized array is a null array.

The following example demonstrates the difference between an empty array, a null array, and an array for which individual elements are null.

SET PHONELIST = ARRAY[];
           /* Set an entire array to empty           */
SET PHONELIST = NULL;
           /* Set an entire array to the NULL value  */
SET PHONELIST = ARRAY[NULL];
           /* Set one element of an array to NULL    */
SET PHONELIST = ARRAY[NULL, NULL, NULL];
           /* Set three elements of an array to NULL */

An ordinary array has a defined upper bound on the number of elements, which is known as the maximum cardinality. Each element in the array is referenced by an associated index value that represents the position of that element in the array. The data type of the index values is INTEGER. If n is the number of elements in an ordinary array, the ordinal position that is associated with each element is an integer value greater than or equal to 1 and less than or equal to n.

Unlike the maximum cardinality of an array in programming languages such as C, the maximum cardinality of an ordinary array in SQL is not related to the physical representation of the array. The amount of memory that is required to represent the value of an ordinary array is usually proportional to the cardinality of the array, and not to the maximum cardinality of the array type. When an ordinary array is referenced, all of the values in the array are stored in main memory. Therefore, ordinary arrays that contain a large amount of data consume large amounts of main memory.

An associative array has no predefined upper bound on the number of elements. An associative array contains an ordered set of zero or more elements, where each element in the array is ordered by and can be referenced by an associated index value. The data type of the index values can be an integer or a character string other than a CLOB, but all index values for the array must have the same data type. The index values of an associative array are unique, and do not need to be contiguous.

A user-defined array type is a user-defined data type that is defined as an array. A global variable, an SQL variable or SQL parameter can be defined as a user-defined array type. Additionally, the result of an invocation of the built-in ARRAY_DELETE or TRIM_ARRAY functions, or the result of a CAST specification, can be a user-defined array type. An element of a user-defined array type can be referenced anywhere that an expression that returns the same data type as an element of that array can be used.

An unnamed array type is an array without an associated user-defined data type. The result of invocation of the aggregate built-in function ARRAY_AGG or of an array constructor is an array without an associated user-defined data type. An element of an array without an associated user-defined array type cannot be directly referenced.

The value of an array index can be specified by an expression. That expression can include a reference to a column. If a column is defined with a column mask, the column mask is applied using the normal rules for applying a column mask.

The value of an index for an array element is never null. If an expression specifies a value for an index, and the expression evaluates to the null value, the null value is returned for the array value.

An array value can be specified using one of the following methods:

• A simple reference to a global variable, an SQL variable, or SQL parameter that is a user-defined array type.
• Invocation of the ARRAY_AGG function.
• Invocation of the ARRAY_DELETE or TRIM_ARRAY built-in functions.
• Use of an array constructor.
• Invocation of a CAST specification that returns an array value.

An array value cannot be stored in a table or returned to an external application.

Datetime data in the elements of an array is considered to be CCSID UNICODE (1208).