15.00 - Rules for Changing Column Data Types - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Rules for Changing Column Data Types

The table on the following pages illustrates the rules for changing a data type from the existing type to a new type.

Notice that there is not a direct correspondence between whether a source type can be explicitly cast to a target type (see SQL Functions, Operators, Expressions, and Predicates) and whether you can change an existing column data type to a new type using an ALTER TABLE request.

You cannot change the character set attribute of an existing column using ALTER TABLE requests. For example, once a column has been defined as LATIN, you cannot change it to UNICODE.

 

Old Data Type

New Data Type

Restrictions on Changes

All

All

Cannot change the data type for a column specified in a primary or secondary index.

  • CHARACTER(n), UC
  • CHARACTER(n)
  • CHARACTER(n)
  • CHARACTER(n), CS
  • Lengths must remain identical. You can only change a case‑specific setting.

    CHARACTER

    CHARACTER(n)

    Not allowed if n > 1.

    CHARACTER

    DATE

    Value trimmed from leading and trailing blanks and handled like a string literal in the declaration of the DATE string literal.

    If conversion is not possible, the system returns an error.

    CHARACTER

    TIME [WITH TIME ZONE]

    Value trimmed from leading and trailing blanks and handled like a string literal in the declaration of the TIME string literal.

    If conversion is not possible, the system returns an error.

    CHARACTER

    TIMESTAMP [WITH TIME ZONE]

    Value trimmed from leading and trailing blanks and handled like a string literal in the declaration of the TIMESTAMP string literal.

    If conversion is not possible, the system returns an error.

    CHARACTER

    INTERVAL

    Value trimmed from leading and trailing blanks and handled like a string literal in the declaration of the INTERVAL string literal.

    If conversion is not possible, the system returns an error.

  • CHARACTER
  • VARCHAR
  • PERIOD

    Not allowed.

    VARCHAR(m), UC

    VARCHAR(n)

    Cannot decrease the maximum length.

    Cannot change from no UC to UC.

    GRAPHIC

    CHARACTER(n) CHARACTER SET GRAPHIC

    Not allowed if n > 1.

    VARGRAPHIC(m)

    VARCHAR(n) CHARACTER SET GRAPHIC

    Cannot decrease the maximum length.

    BYTE

    BYTE(n)

    Not allowed if n > 1.

    VARBYTE(m)

    VARBYTE(n)

    Cannot decrease the maximum length.

    Exact NUMERIC

    INTERVAL

    INTERVAL must have a single field only and its numeric value must be in the range permitted for an INTERVAL value. Otherwise, the system returns an error.

    INTEGER

    DATE

    Cannot make this change because an INTEGER column could include non‑valid DATE values.

    DECIMAL(n,0)

    INTEGER

    Can change only if n is in the range 5 - 9, inclusive.

    DECIMAL(n,0)

    BIGINT

    Can change only if n is in the range 10 - 18, inclusive.

    DECIMAL(n,0)

    SMALLINT

    Can change only if n is 3 or 4.

    DECIMAL(n,0)

    BYTEINT

    Can change only if n = 1 or 2.

    DECIMAL(n,f)

    DECIMAL(m,f)

    Can change only as follows.

  • m >= n, no change in f
  • n = 1 or 2, m < 3
  • n = 3 or 4, m < 5
  • n = 5 to 9, m < 10
  • n = 10 to 15, m <= 18
  • DATE

    INTEGER

    No restrictions.

    DATE

    DATE

    No restrictions.

    DATE

    CHARACTER

    No restrictions.

    Result might not be in ANSI date format.

    DATE

    TIME [WITH TIME ZONE]

    No restrictions.

    DATE

    TIMESTAMP [WITH TIME ZONE]

    Year, month, and day are taken from source date value. Hour, minute, and seconds are set to 0.

    If target specifies TIMESTAMP WITH TIME ZONE, the time zone fields are taken from the explicit or implicit values for the source.

    DATE

    PERIOD

    Not allowed.

    TIME [WITH TIME ZONE]

    TIME [WITH TIME ZONE]

    If target is TIME WITH TIME ZONE, then time zone displacement value is added. Otherwise, value is adjusted to the current time zone displacement for the session.

  • TIME [WITH TIME ZONE]
  • TIMESTAMP [WITH TIME ZONE]
  • INTERVAL
  • CHARACTER(n)

    n must be >= the length of the value.

    If n > the value, then trailing blanks are added.

    If n < the value, an error is reported.

  • TIME [WITH TIME ZONE]
  • TIMESTAMP [WITH TIME ZONE]
  • INTERVAL
  • VARCHAR

    Same as conversion to CHARACTER except no trailing blanks are added when n > value.

    TIME [WITH TIME ZONE]

    TIMESTAMP [WITH TIME ZONE]

    No restriction.

    Year, month, and day are set to the values for CURRENT_DATE.

    If target also specifies TIME ZONE, the time zone values are taken from the explicit or implicit values for the source.

    TIME

    PERIOD

    Not allowed.

    TIME WITH TIME ZONE

    TIME

    No restriction, but TIME ZONE value is removed.

  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE
  • DATE

    Result is year, month, and day from TIMESTAMP value after adjustment for TIME ZONE, if present.

    TIMESTAMP [WITH TIME ZONE]

    TIMESTAMP [WITH TIME ZONE]

    No restriction.

    If source and target differ on value for WITH TIME ZONE, conversion is required, which can change the values in the result.

    TIMESTAMP [WITH TIME ZONE]

    TIME

    Result is taken from hour, minute, and second fields.

    If target is TIME WITH TIME ZONE, then time zone displacement is added. Otherwise value is adjusted to the current time zone displacement of the session.

    TIMESTAMP [WITH TIME ZONE]

    PERIOD

    Not allowed.

    INTERVAL (n)

    INTERVAL (m)

    Can change only if m >= n and is YEAR‑MONTH‑ or DAY‑TIME‑compatible.

    You cannot mix YEAR‑MONTH intervals with DAY‑TIME intervals.

    PERIOD

  • CHARACTER(n)
  • VARCHAR(n)
  • Not allowed.

    PERIOD

    PERIOD

    Not allowed.

    PERIOD

    DATE

    Not allowed.

    PERIOD

    TIME [WITH TIME ZONE]

    Not allowed.

    PERIOD

    TIMESTAMP [WITH TIME ZONE]

    Not allowed.

    UDT

    Not allowed.

    Not allowed.

    You cannot convert a column UDT data type to any other data type using an ALTER TABLE request.

  • ARRAY (one‑dimensional)
  • VARRAY (one‑dimensional)
  • Not allowed.

    Not allowed.

    You cannot convert a one‑dimensional column ARRAY data type to any other data type using an ALTER TABLE request.

  • ARRAY (multidimensional)
  • VARRAY (multidimensional)
  • Not allowed.

    Not allowed.

    You cannot convert a multidimensional column ARRAY data type to any other data type using an ALTER TABLE request.