ALTER TYPE

On this page Carat arrow pointing down

The ALTER TYPE statement is part of ALTER TABLE and changes a column's data type.

Tip:

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

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

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:

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:

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

See also


Yes No
On this page

Yes No