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 an INT, 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] to STRING[20], but not to STRING [5] since that will invalidate the existing data.

Synopsis

ALTER TABLE IF EXISTS table_name ALTER COLUMN column_name SET DATA TYPE typename

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

copy
icon/buttons/copy
> 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:

copy
icon/buttons/copy
> 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:

copy
icon/buttons/copy
> ALTER TABLE customer ALTER c_credit_lim type DECIMAL (8,2);
pq: type conversion not yet implemented

See also



Yes No