Before you can use an ARRAY data type, you must first create it using the CREATE TYPE statement. When creating a new ARRAY type, you must specify:
- A name for the ARRAY type.
- The data type of the ARRAY elements.
For a 1-D ARRAY type, you also specify a maximum number of elements in the ARRAY.
For an n-D ARRAY type, you specify the number of dimensions from 2 to 5, with a pair of lower and upper boundaries for each dimension. You can specify the array boundary dimensions using any combination of the following two methods:
- Explicitly specify lower and upper bounds for each dimension, separating the two with a colon. For example, [n:m] where n and m are signed integer values, meaning that negative numbers are allowed.
- Specify a single value to signify the maximum size of the dimension using ANSI-style syntax, which implicitly defines the lower bound of the array to be 1. For example, [n] where n is an unsigned (positive) integer value.
If you specify the optional DEFAULT NULL clause when creating the ARRAY type, all elements are set to NULL when an instance of the ARRAY type is constructed. Otherwise, all elements are set to an uninitialized state. You will receive an error if you try to access an element that is in an uninitialized state.
For more information, see CREATE TYPE (ARRAY Form) in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.