16.20 - ANSI Compliance - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ Data Types and Literals

Teradata Database
Teradata Vantage NewSQL Engine
March 2019
Programming Reference

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.