Yang, Tong 3f5759eed2 MRS comp-lts 2.0.38.SP20 version

Reviewed-by: Hasko, Vladimir <vladimir.hasko@t-systems.com>
Co-authored-by: Yang, Tong <yangtong2@huawei.com>
Co-committed-by: Yang, Tong <yangtong2@huawei.com>

2023-01-19 17:08:45 +00:00

8.5 KiB

Raw Permalink Blame History

CHANGE DATA TYPE

Function

This command is used to change the data type from INT to BIGINT or decimal precision from lower to higher.

Syntax

ALTER TABLE [db_name.]table_name CHANGE col_name col_name changed_column_type;

Parameter Description

**Table 1** CHANGE DATA TYPE parameters
Parameter	Description
db_name	Name of the database. If this parameter is left unspecified, the current database is selected.
table_name	Name of the table.
col_name	Name of columns in a table. Column names contain letters, digits, and underscores (_).
changed_column_type	The change in the data type.

Usage Guidelines

Change of decimal data type from lower precision to higher precision will only be supported for cases where there is no data loss.
Example:
- Invalid scenario - Change of decimal precision from (10,2) to (10,5) is not valid as in this case only scale is increased but total number of digits remain the same.
- Valid scenario - Change of decimal precision from (10,2) to (12,3) is valid as the total number of digits are increased by 2 but scale is increased only by 1 which will not lead to any data loss.

The allowed range is 38,38 (precision, scale) and is a valid upper case scenario which is not resulting in data loss.

Examples

Changing data type of column a1 from INT to BIGINT.
ALTER TABLE test_db.carbon CHANGE a1 a1 BIGINT;
Changing decimal precision of column a1 from 10 to 18.
ALTER TABLE test_db.carbon CHANGE a1 a1 DECIMAL(18,2);

System Response

By running DESCRIBE command, the changed data type for the modified column is displayed.

Parent topic: DDL

8.5 KiB Raw Permalink Blame History