Partitioning Expression Data Type Considerations - Teradata Database

Teradata Database Design
Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
Product Category
Software

Partitioning Expression Data Type Considerations

Note: In addition to the following data type rules and restrictions, be aware that you cannot specify a row‑level security constraint column in a partitioning expression.

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

The following table summarizes these restrictions.

 

                            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 they are 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, then it must be such that it can be cast 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.