Changing Column Data Types | ALTER TABLE | VantageCloud Lake - Rules for Changing Column Data Types - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

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

There is no direct correspondence between whether a source type can be explicitly cast to a target type (see Data Type Compatibility and Conversion) and whether you can change an existing column data type to a new type using an ALTER TABLE request.

You cannot use ALTER TABLE to change the character set attribute of an existing column. For example, you cannot change a LATIN column to UNICODE.

If you change the data type of a column, Vantage no longer uses the statistics that reference the column. To use the statistics, recollect them.

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 can 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 may 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.