Skip to content

Latest commit

 

History

History
89 lines (60 loc) · 2.93 KB

alter-type.md

File metadata and controls

89 lines (60 loc) · 2.93 KB
Raw
title summary toc
ALTER TYPE
Use the ALTER TYPE statement to change a column's data type.
true

The ALTER TYPE statement is part of ALTER TABLE.

{{site.data.alerts.callout_info}} In CockroachDB versions < v20.2, support for altering column types is limited to increasing precision. For details, see Limitations. {{site.data.alerts.end}}

Limitations

In CockroachDB versions < v20.2, support for altering column types is limited to increasing the precision of the current type of a column. You cannot convert the column type to another data type, or decrease the precision of the column type.

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

{% include {{ page.version.version }}/sql/diagrams/alter_type.html %}

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.
ALTER TABLE ... ALTER TYPE and ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE are aliases.

Viewing schema changes

{% include {{ page.version.version }}/misc/schema-change-view-job.md %}

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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

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

See also