ALTER TYPE

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

ALTERTABLEIFEXISTStable_nameALTERCOLUMNcolumn_nameSETDATATYPEtypename

Required privileges

The user must have the CREATE privilege on the table.

Parameters

ParameterDescription
table_nameThe name of the table with the column whose data type you want to change.
column_nameThe name of the column whose data type you want to change.
typenameThe 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):

  1. > ALTER TABLE customer ALTER c_credit_lim type DECIMAL (12,2);
  1. ALTER TABLE
  3. 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:

  1. > ALTER TABLE customer ALTER c_credit_lim type INT;
  1. 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:

  1. > ALTER TABLE customer ALTER c_credit_lim type DECIMAL (8,2);
  1. pq: type conversion not yet implemented

See also

Was this page helpful?
YesNo