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. |
|
|
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. |
|
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.
|
| 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. |
|
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. |
|
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. |
|
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 |
|
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. |
|
Not allowed. | Not allowed. You cannot convert a one-dimensional column ARRAY data type to any other data type using an ALTER TABLE request. |
|
Not allowed. | Not allowed. You cannot convert a multidimensional column ARRAY data type to any other data type using an ALTER TABLE request. |