16.20 - Rules for Changing Column Data Types - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

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 Teradata Vantage™ Data Types and Literals, B035-1143) 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.
  • mn, 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 mn 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.