The ALTER TYPE statement is part of ALTER TABLE and changes a column's data type.
New in v19.1: This command can be combined with other ALTER TABLE commands in a single statement. For a list of commands that can be combined, see ALTER TABLE. For a demonstration, see Add and rename columns atomically.
Considerations
You can use the ALTER TYPE subcommand if the following conditions are met:
- On-disk representation of the column remains unchanged. For example, you cannot change the column data type from
STRINGto anINT, even if the string is just a number. - The existing data remains valid. For example, you can change the column data type from
STRING[10]toSTRING[20], but not toSTRING [5]since that will invalidate the existing data.
Synopsis
Required privileges
The user must have the CREATE privilege on the table.
Parameters
| Parameter | Description |
|---|---|
table_name |
The name of the table with the column whose data type you want to change. |
column_name |
The name of the column whose data type you want to change. |
typename |
The new data type you want to use. |
Viewing schema changes
This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.
Examples
Success scenario
The TPC-C database has a customer table with a column c_credit_lim DECIMAL (10,2). Suppose you want to change the data type to DECIMAL (12,2):
> ALTER TABLE customer ALTER c_credit_lim type DECIMAL (12,2);
ALTER TABLE
Time: 80.814044ms
Error scenarios
Changing a column data type from DECIMAL to INT would change the on-disk representation of the column. Therefore, attempting to do so results in an error:
> ALTER TABLE customer ALTER c_credit_lim type INT;
pq: type conversion not yet implemented
Changing a column data type from DECIMAL(12,2) to DECIMAL (8,2) would invalidate the existing data. Therefore, attempting to do so results in an error:
> ALTER TABLE customer ALTER c_credit_lim type DECIMAL (8,2);
pq: type conversion not yet implemented
