17.10 - ANSI Compliance - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Data Types and Literals

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)
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.
  • Vantage 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. Vantage 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.