15.10 - ARRAY/VARRAY Data Type - Teradata Database

A data type used for storing and accessing multidimensional data. The ARRAY data type can store many values of the same specific data type in a sequential or matrix-like format.

where:

Syntax element …	Specifies …
SYSUDTLIB.	the name of the database in which all ARRAY data types are created.
array_type_name	the name of an ARRAY data type that was created with a CREATE TYPE statement.
attribute	appropriate data type attributes. An ARRAY column supports the following attributes:
	NULL NOT NULL FORMAT	TITLE NAMED DEFAULT NULL
	For details on using data type attributes with ARRAY data types, see: Chapter 11: “Default Value Control Phrases” Chapter 12: “Data Type Formats and Format Phrases” An ARRAY data type column does not support column storage or column constraint attributes.

The one-dimensional (1-D) ARRAY data type is partially compliant with the ANSI SQL:2011 standard. The 1-D ARRAY type is not compatible with the ANSI standard in the following areas:

You must create an ARRAY type, using the CREATE TYPE statement, before you can use the ARRAY type. This is not an ANSI requirement.

The Teradata ARRAY data type is a user-defined type (UDT). This differs from the ANSI standard which does not consider an ARRAY data type to be a UDT.

Teradata syntax for the ARRAY value constructor is based on the UDT constructor syntax. This differs from the ANSI syntax.

ANSI-style comparison of two arrays (A=B) is not supported. However, comparison on individual elements of two arrays, that is A[2]=B[2], is supported. You can use the ARRAY_COMPARE system function to perform equals/not equals comparison on all the elements of two arrays.

Teradata Database provides a built-in CAST operation for ARRAY types that performs a CAST from VARCHAR to ARRAY, and ARRAY to VARCHAR. This differs from ANSI ARRAY CAST functionality, where all of the elements in a source array are cast to the element type of a target array.

The concatenation operator requires that both operands are the same ARRAY type and that the target type of the concatenation operation is also the same type. This is a deviation from the ANSI standard since it defines the target data type of a 1-D ARRAY concatenation to be a new 1-D ARRAY type with the length defined as the sum of the length of both operands.

The ARRAY_AGG system aggregate function includes an additional parameter containing an ARRAY expression of the target ARRAY type. This eliminates ambiguity since you can define multiple ARRAY types that have the same element data type.

An ARRAY constructor by query is not supported. You can use the ARRAY_AGG system aggregate function to perform this type of operation.

The multidimensional (n-D) ARRAY data type is a Teradata extension to the ANSI SQL standard. Teradata Database extends the ANSI SQL:2011 syntax for the 1-D ARRAY type to permit multiple dimensions.

Both 1-D and n-D ARRAY data types store and manage their element values in a way that is compliant with the ANSI SQL:2011 standard.

The 1-D ARRAY type is defined as a variable-length ordered list of values of the same data type. It has a maximum number of values that you specify when you create the ARRAY type. You can access each element value in a 1-D ARRAY type using a numeric index value. For details on referencing an ARRAY element, see “ARRAY Functions and Operators” in SQL Functions, Operators, Expressions, and Predicates.

Teradata Database stores the element values of a 1-D ARRAY sequentially starting with the first element, from left-to-right.

The 1-D ARRAY type is compatible with the VARRAY type provided by Oracle Database except for the following areas:

The syntax for ARRAY methods that have zero parameters requires an empty set of parenthesis.

Some methods that provide the same functionality as methods defined for Oracle VARRAY types have slightly different names in Teradata. For example, Oracle provides the FIRST method, but Teradata provides the OFIRST method. The renaming was done to avoid conflicts with Teradata reserved words.

Teradata Database provides a set of methods that have the same functionality as some methods defined for Oracle VARRAY types. However, not all methods defined for Oracle VARRAY types are available for Teradata ARRAY types.

The ALTER TYPE statement is not supported for Teradata ARRAY types. It is supported for Oracle VARRAY types.

An n-D ARRAY is a mapping from integer coordinates to an element type. The n-D ARRAY type is defined as a variable-length ordered list of values of the same data type. It has 2-5 dimensions, with a maximum number of values for each dimension, which you specify when you create the ARRAY type.

You define an n-D ARRAY type with a pair of lower and upper boundaries [n:m] for each of its dimensions. Alternatively, you can specify a single value [n] to signify the maximum size of a dimension, which implicitly defines the lower bound of the dimension to be 1. For details, see “Creating an ARRAY Data Type” on page 255.

You can access each element value in an n-D ARRAY type using numeric index values for each dimension. For details on referencing an ARRAY element, see “ARRAY Functions and Operators” in SQL Functions, Operators, Expressions, and Predicates.

The figure below shows the constituents of a 3-D ARRAY data type.

Teradata Database stores the element values of an n-D ARRAY in row-major order. Row-major means that the first dimension, which is leftmost in the scope specification, is the most major dimension, and as you move toward the last dimension, the dimensions become less and less major.

An ARRAY data type is a UDT, which means that Teradata Database will store any ARRAY type that you create in the SYSUDTLIB database along with its autogenerated constructor UDF, by default. Therefore, in order to create an ARRAY data type, you must have either the UDTTYPE or UDTMETHOD privilege on the SYSUDTLIB database.

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 detailed information, see “CREATE TYPE (ARRAY Form)” in SQL Data Definition Language.

The data type of an ARRAY element can be any Teradata data type except for the following:

BLOB

CLOB

LOB UDTs (both distinct and structured types)

Geospatial

Parameter data types, such as TD_ANYTYPE or VARIANT_TYPE

ARRAY

Note that you cannot specify an ARRAY data type as the element type of an ARRAY type.

The following online HELP statement lists all of the valid element data types for an ARRAY data type:

   HELP 'array_data_type declarations';

Notice:

KANJI1 support is deprecated. KANJI1 is not allowed as a default character set. The system changes the KANJI1 default character set to the UNICODE character set. Creation of new KANJI1 objects is highly restricted. Although many KANJI1 queries and applications may continue to operate, sites using KANJI1 should convert to another character set as soon as possible.

Teradata Database automatically generates the following routines for each ARRAY data type you create.

Autogenerated Routine	Description
Constructor function	Allocates an ARRAY type instance and sets all the elements to an uninitialized state if the DEFAULT NULL clause was not specified. If DEFAULT NULL was specified, all elements are set to null.
Constructor method	Takes one or more arguments and initializes each element of the array with the corresponding value passed to the method.

Use the ARRAY constructor expression to create a new instance of an ARRAY data type and initialize it using the autogenerated constructor method or function. For details about the ARRAY constructor expression, see “ARRAY Functions and Operators” in SQL Functions, Operators, Expressions, and Predicates.

Teradata Database automatically generates the following functionality for each ARRAY data type you create.

Autogenerated Functionality	Description
ARRAY type transform	from-sql and to-sql functionality associated with the transform of an ARRAY type. The ARRAY values are transformed to/from a VARCHAR(length) value, where length depends on the element type and the total number of elements defined in the ARRAY. For detailed information about transform input/output strings for ARRAY types, see Appendix B: “External Representations for UDTs.”
ARRAY type ordering	Basic ordering functionality for an ARRAY type.
ARRAY type cast	Casting functionality for an ARRAY type. Two autogenerated casts are provided: VARCHAR to ARRAY, and ARRAY to VARCHAR.

For detailed information, see “CREATE TYPE (ARRAY Form)” in SQL Data Definition Language.

The 1‑D ARRAY type only supports a lower bound of 1. This means that every 1-D ARRAY type that is created has a first array element indexed by the number 1.

The n-D ARRAY type supports an optional user-specified lower bound that can be a negative or positive integer number.

The maximum size of an ARRAY type and its autogenerated transform string must not exceed 64 KB because Teradata Database stores its data within the row and because the maximum size of its autogenerated transform string is limited to the maximum size of a VARCHAR type, which is 64,000 Teradata Latin bytes.

Note that the size of the array is influenced not only by the number of elements, but also by the element type of the array.

For an n-D ARRAY type, you must first consider the row size limit of 64 KB. Then there is a limit to the maximum number of dimensions that can be declared within the scope of the array. The minimum number of dimensions that you can create is 2, and the maximum number of dimensions that you can create is 5.

You can use the optional DEFAULT NULL clause when creating an ARRAY type to initialize all elements of the ARRAY to null at the time a new instance of the ARRAY type is constructed.

This clause is particularly useful for ARRAY types that are expected to be fully populated.

When you use this clause, the action prevents all subset operations such as AVERAGE, UPDATE, or COUNT for arrays from aborting and returning an error because they refer to an element that is not initialized.

Teradata Database provides system functions, operators, and expressions that operate on either 1-D, n-D, or both ARRAY data types. These functions allow you to perform arithmetic, relational, and aggregate operations on an array in its entirety or on a subset of the elements composing the array. For information about these functions, see “ARRAY Functions and Operators” in SQL Functions, Operators, Expressions, and Predicates.

The ARRAY data type cannot be used as a data type for input or output parameters in UDFs, UDMs, or external stored procedures written in the Java language.

You cannot pass ARRAY values as arguments to the search condition in a WHERE clause. You can pass individual elements of an ARRAY to the search condition in a WHERE clause. Also, you may use system functions provided by Teradata Database to do relational comparison on ARRAY data. For information about these functions, see “ARRAY Functions and Operators” in SQL Functions, Operators, Expressions, and Predicates.

You cannot specify an ARRAY column in any of the following SQL DML clauses:

INTERSECT, MINUS, or UNION set operator

DISTINCT operator

ORDER BY, GROUP BY, or HAVING clause of a SELECT statement

You cannot create UDMs for an ARRAY type. The only valid methods for an ARRAY type are the methods that Teradata Database creates automatically for an ARRAY type. Therefore, you cannot specify an ARRAY type name in the FOR clause of a CREATE/REPLACE METHOD statement.

An ARRAY column cannot be a component of an index.

Any restrictions that apply to a UDT also apply to an ARRAY type.

The following statement uses Teradata syntax to create a 1-D ARRAY type with 5 elements of type CHAR(10). All elements of the array are initialized to null.

   CREATE TYPE phonenumbers_ary AS CHAR(10) ARRAY[5] DEFAULT NULL;

The following statement uses Oracle-compatible syntax to create a 1-D ARRAY type with 5 elements of type CHAR(10). All elements of the array are set to an uninitialized state.

   CREATE TYPE phonenumbers_ary AS VARRAY(5) OF CHAR(10);

Consider the following structured UDT:

   CREATE TYPE measures_UDT AS(amplitude INTEGER, 

                               phase     INTEGER, 

                               frequency INTEGER);

The following statement uses Teradata syntax to create a 3-D ARRAY type with elements of type measures_UDT. The scope of this array is composed of three dimensions:

Dimension one has a lower bound of 1 and an upper bound of 5.

Dimension two has a lower bound of 1 and an upper bound of 7.

Dimension three has a lower bound of 1 and an upper bound of 20.

   CREATE TYPE seismic_cube AS measures_UDT ARRAY [1:5][1:7][1:20];

The following statement uses Oracle-compatible syntax to create the same 3-D ARRAY type.

   CREATE TYPE seismic_cube AS VARRAY (1:5)(1:7)(1:20) OF measures_UDT;

Consider the following 1-D ARRAY type:

   CREATE TYPE phonenumbers_ary AS CHAR(10) ARRAY[5] DEFAULT NULL;

The following statement creates a table with a column named ephone that has a data type of phonenumbers_ary, which is a 1-D ARRAY type.

   CREATE TABLE my_table (eno INTEGER, ephone phonenumbers_ary);

FOR information on …	SEE …
creating an ARRAY data type	“CREATE TYPE (ARRAY Form)” in SQL Data Definition Language.
creating a new instance of an ARRAY data type and initializing it	“ARRAY Functions and Operators” in SQL Functions, Operators, Expressions, and Predicates.
accessing the element values of an ARRAY data type	“ARRAY Functions and Operators” in SQL Functions, Operators, Expressions, and Predicates.
system functions, operators, and expressions that operate on ARRAY data types	“ARRAY Functions and Operators” in SQL Functions, Operators, Expressions, and Predicates.
writing routines that use ARRAY input and output parameters	SQL External Routine Programming.
transform input/output strings for ARRAY types	Appendix B: “External Representations for UDTs.”