The ALTER COLUMN statement is part of ALTER TABLE and can be used to:

Note:

To manage other constraints, see ADD CONSTRAINT and DROP CONSTRAINT.

Note:

Support for altering column types is experimental in CockroachDB versions v20.2, with certain limitations. For details, see Altering column data types.

Tip:

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.

Synopsis

ALTER TABLE IF EXISTS table_name ALTER COLUMN column_name SET DEFAULT a_expr NOT NULL DATA TYPE typename COLLATE collation_name USING a_expr DROP DEFAULT NOT NULL STORED TYPE typename COLLATE collation_name USING a_expr

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 you want to modify.
column_name The name of the column you want to modify.
SET DEFAULT a_expr The new Default Value you want to use.
typename The new data type you want to use.
New in v20.2: Support for altering column types is expanded in CockroachDB v20.2, but still experimental, and with certain limitations. For details, see Altering column data types.
USING a_expr New in v20.2: Specifies how to compute a new column value from the old column value.

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Altering column data types

New in v20.2: Support for altering column data types is experimental in CockroachDB versions v20.2, with certain limitations. To enable column type altering, set the enable_experimental_alter_column_type_general session variable to true.

The following are equivalent in CockroachDB:

  • ALTER TABLE ... ALTER ... TYPE
  • ALTER TABLE ... ALTER COLUMN TYPE
  • ALTER TABLE ... ALTER COLUMN SET DATA TYPE

For examples of ALTER COLUMN TYPE, Examples.

Limitations on altering data types

You can alter the data type of a column if:

Note:

Most ALTER COLUMN TYPE changes are finalized asynchronously. Schema changes on the table with the altered column may be restricted, and writes to the altered column may be rejected until the schema change is finalized.

Examples

Set or change a DEFAULT value

Setting the DEFAULT value constraint inserts the value when data's written to the table without explicitly defining the value for the column. If the column already has a DEFAULT value set, you can use this statement to change it.

The below example inserts the Boolean value true whenever you inserted data to the subscriptions table without defining a value for the newsletter column.

copy
icon/buttons/copy
> ALTER TABLE subscriptions ALTER COLUMN newsletter SET DEFAULT true;

Remove DEFAULT constraint

If the column has a defined DEFAULT value, you can remove the constraint, which means the column will no longer insert a value by default if one is not explicitly defined for the column.

copy
icon/buttons/copy
> ALTER TABLE subscriptions ALTER COLUMN newsletter DROP DEFAULT;

Set NOT NULL constraint

Setting the NOT NULL constraint specifies that the column cannot contain NULL values.

copy
icon/buttons/copy
> ALTER TABLE subscriptions ALTER COLUMN newsletter SET NOT NULL;

Remove NOT NULL constraint

If the column has the NOT NULL constraint applied to it, you can remove the constraint, which means the column becomes optional and can have NULL values written into it.

copy
icon/buttons/copy
> ALTER TABLE subscriptions ALTER COLUMN newsletter DROP NOT NULL;

Convert a computed column into a regular column

You can convert a stored, computed column into a regular column by using ALTER TABLE.

In this example, create a simple table with a computed column:

copy
icon/buttons/copy
> CREATE TABLE office_dogs (
    id INT PRIMARY KEY,
    first_name STRING,
    last_name STRING,
    full_name STRING AS (CONCAT(first_name, ' ', last_name)) STORED
  );

Then, insert a few rows of data:

copy
icon/buttons/copy
> INSERT INTO office_dogs (id, first_name, last_name) VALUES
    (1, 'Petee', 'Hirata'),
    (2, 'Carl', 'Kimball'),
    (3, 'Ernie', 'Narayan');
copy
icon/buttons/copy
> SELECT * FROM office_dogs;
+----+------------+-----------+---------------+
| id | first_name | last_name |   full_name   |
+----+------------+-----------+---------------+
|  1 | Petee      | Hirata    | Petee Hirata  |
|  2 | Carl       | Kimball   | Carl Kimball  |
|  3 | Ernie      | Narayan   | Ernie Narayan |
+----+------------+-----------+---------------+
(3 rows)

The full_name column is computed from the first_name and last_name columns without the need to define a view. You can view the column details with the SHOW COLUMNS statement:

copy
icon/buttons/copy
> SHOW COLUMNS FROM office_dogs;
+-------------+-----------+-------------+----------------+------------------------------------+-------------+
| column_name | data_type | is_nullable | column_default |       generation_expression        |   indices   |
+-------------+-----------+-------------+----------------+------------------------------------+-------------+
| id          | INT       |    false    | NULL           |                                    | {"primary"} |
| first_name  | STRING    |    true     | NULL           |                                    | {}          |
| last_name   | STRING    |    true     | NULL           |                                    | {}          |
| full_name   | STRING    |    true     | NULL           | concat(first_name, ' ', last_name) | {}          |
+-------------+-----------+-------------+----------------+------------------------------------+-------------+
(4 rows)

Now, convert the computed column (full_name) to a regular column:

copy
icon/buttons/copy
> ALTER TABLE office_dogs ALTER COLUMN full_name DROP STORED;

Check that the computed column was converted:

copy
icon/buttons/copy
> SHOW COLUMNS FROM office_dogs;
+-------------+-----------+-------------+----------------+-----------------------+-------------+
| column_name | data_type | is_nullable | column_default | generation_expression |   indices   |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
| id          | INT       |    false    | NULL           |                       | {"primary"} |
| first_name  | STRING    |    true     | NULL           |                       | {}          |
| last_name   | STRING    |    true     | NULL           |                       | {}          |
| full_name   | STRING    |    true     | NULL           |                       | {}          |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
(4 rows)

The computed column is now a regular column and can be updated as such:

copy
icon/buttons/copy
> INSERT INTO office_dogs (id, first_name, last_name, full_name) VALUES (4, 'Lola', 'McDog', 'This is not computed');
copy
icon/buttons/copy
> SELECT * FROM office_dogs;
+----+------------+-----------+----------------------+
| id | first_name | last_name |      full_name       |
+----+------------+-----------+----------------------+
|  1 | Petee      | Hirata    | Petee Hirata         |
|  2 | Carl       | Kimball   | Carl Kimball         |
|  3 | Ernie      | Narayan   | Ernie Narayan        |
|  4 | Lola       | McDog     | This is not computed |
+----+------------+-----------+----------------------+
(4 rows)

Convert to a different data type

The TPC-C database has a customer table with a column c_credit_lim of type DECIMAL(10,2):

copy
icon/buttons/copy
> SELECT column_name, data_type FROM [SHOW COLUMNS FROM customer] WHERE column_name='c_credit_lim';
  column_name  |   data_type
---------------+----------------
  c_credit_lim | DECIMAL(10,2)
(1 row)

Suppose you want to change the data type from DECIMAL to STRING.

First, set the enable_experimental_alter_column_type_general session variable to true:

copy
icon/buttons/copy
> SET enable_experimental_alter_column_type_general = true;

Then, alter the column type:

copy
icon/buttons/copy
> ALTER TABLE customer ALTER c_credit_lim TYPE STRING;
NOTICE: ALTER COLUMN TYPE changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes; some writes to the altered column may be rejected until the schema change is finalized
copy
icon/buttons/copy
> SELECT column_name, data_type FROM [SHOW COLUMNS FROM customer] WHERE column_name='c_credit_lim';
  column_name  | data_type
---------------+------------
  c_credit_lim | STRING
(1 row)

Change a column type's precision

The TPC-C customer table contains a column c_balance of type DECIMAL(12,2):

copy
icon/buttons/copy
> SELECT column_name, data_type FROM [SHOW COLUMNS FROM customer] WHERE column_name='c_balance';
  column_name |   data_type
--------------+----------------
  c_balance   | DECIMAL(12,2)
(1 row)

Suppose you want to increase the precision of the c_balance column from DECIMAL(12,2) to DECIMAL(14,2):

copy
icon/buttons/copy
> ALTER TABLE customer ALTER c_balance TYPE DECIMAL(14,2);
copy
icon/buttons/copy
> SELECT column_name, data_type FROM [SHOW COLUMNS FROM customer] WHERE column_name='c_balance';
  column_name |   data_type
--------------+----------------
  c_balance   | DECIMAL(14,2)
(1 row)

Change a column's type using an expression

You can change the data type of a column and create a new, computed value from the old column values, with a USING clause. For example:

copy
icon/buttons/copy
> SELECT column_name, data_type FROM [SHOW COLUMNS FROM customer] WHERE column_name='c_discount';
  column_name |  data_type
--------------+---------------
  c_discount  | DECIMAL(4,4)
(1 row)
copy
icon/buttons/copy
> SELECT c_discount FROM customer LIMIT 10;
  c_discount
--------------
      0.1569
      0.4629
      0.2932
      0.0518
      0.3922
      0.1106
      0.0622
      0.4916
      0.3072
      0.0316
(10 rows)
copy
icon/buttons/copy
> ALTER TABLE customer ALTER c_discount TYPE STRING USING ((c_discount*100)::DECIMAL(4,2)::STRING || ' percent');
NOTICE: ALTER COLUMN TYPE changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes; some writes to the altered column may be rejected until the schema change is finalized
copy
icon/buttons/copy
> SELECT column_name, data_type FROM [SHOW COLUMNS FROM customer] WHERE column_name='c_discount';
  column_name | data_type
--------------+------------
  c_discount  | STRING
(1 row)
copy
icon/buttons/copy
> SELECT c_discount FROM customer LIMIT 10;
   c_discount
-----------------
  15.69 percent
  46.29 percent
  29.32 percent
  5.18 percent
  39.22 percent
  11.06 percent
  6.22 percent
  49.16 percent
  30.72 percent
  3.16 percent
(10 rows)

See also



Yes No