Partitioning Expression Data Type Considerations - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

The partitioning expressions you can define for a partitioned table have restrictions regarding the data types you can specify within the expressions, and with respect to the data type of the result of the function.

The following table summarizes these restrictions. Also, you cannot specify a row-level security constraint column in a partitioning expression.

                            Data Type                                     PARTITION BY
     RANGE_N       CASE_N     Expression
  • ARRAY
  • VARRAY
              N              N              N
BIGINT              Y              X              I
BLOB              N              N              N
BYTE              X              X              X
BYTEINT              Y              X              I
CHARACTER              Y              X              I
CLOB              N              N              N
DATE              Y              X              I
  • DECIMAL
  • NUMERIC
  • NUMBER (exact form)
              X              X              I
  • DOUBLE PRECISION
  • FLOAT
  • REAL
  • NUMBER (approximate form)
              X              X              I
GRAPHIC              N              X              N
INTEGER              Y              X              Y
INTERVAL YEAR              X              X              I
INTERVAL YEAR TO MONTH              X              X              X
INTERVAL MONTH              X              X              I
INTERVAL DAY              X              X              I
INTERVAL DAY TO HOUR              X              X              X
INTERVAL DAY TO SECOND              X              X              X
INTERVAL SECOND              X              X              X
LONG VARCHAR              Y              X              I
LONG VARCHAR CHARACTER SET GRAPHIC              N              N              N
PERIOD

The BEGIN and END bound functions are valid in a partitioning expression when defined on a valid PERIOD data type column and the result can be cast implicitly to a numeric data type.

              N              X              N
SMALLINT              Y              X              I
TIME              X              X              X
TIME WITH TIME ZONE              X              X              X
TIMESTAMP              Y              X              X
TIMESTAMP WITH TIME ZONE              Y              X              X
UDT              N              N              N
VARBYTE              X              X              X
VARCHAR              Y              X              I
VARCHAR(n) CHARACTER SET GRAPHIC              N              N              N
  • XML
  • XMLTYPE
              N              N              N

The following table explains the abbreviations used in the previous table.

Symbol Definition
I Valid for a partitioning expression.

If the type is also the data type of the result, the type must be castable to a valid INTEGER or BIGINT value.
N Not valid for a partitioning expression.

If the partitioning expression is defined using a CASE_N function, then this type is not valid for the CASE_N condition.
X Valid for a partitioning expression, but cannot be the data type of the result of the expression.

If the partitioning expression is defined using a CASE_N function, then this type is valid for the CASE_N condition.
Y Valid for a partitioning expression and valid as the data type of the result of the partitioning expression.