CHANGE DATA TYPE

Updated on 2022-11-18 GMT+08:00

View PDF

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

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

**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.

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.

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);