New in v2.1: The ALTER TYPE
statement is part of ALTER TABLE
and changes a column's data type.
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
STRING
to 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. |
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