The ALTER COLUMN
statement is part of ALTER TABLE
and can be used to:
- Set, change, or drop a column's
DEFAULT
constraint - Set or drop a column's
NOT NULL
constraint - Increase the precision of the column's data type
To manage other constraints, see ADD CONSTRAINT
and DROP CONSTRAINT
.
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
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, altered type you want to use. In CockroachDB versions < v21.1, support for altering column types is limited to increasing the precision of the current column type. For details, see Altering column 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 types
In CockroachDB versions < v21.1, 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. Changing the column type from its current type to the same type and precision will result in a no-op, with no error.
You can use ALTER COLUMN TYPE
if the following conditions are met:
- The on-disk representation of the column remains unchanged. For example, you cannot change the column data type from
STRING
to anINT
. - The existing data remains valid. For example, you can change the column data type from
STRING[10]
toSTRING[20]
, but not toSTRING [5]
since that will invalidate the existing data.
The following are equivalent in CockroachDB:
ALTER TABLE ... ALTER ... TYPE
ALTER TABLE ... ALTER COLUMN TYPE
ALTER TABLE ... ALTER COLUMN SET DATA TYPE
For an example of ALTER COLUMN TYPE
, see Increase a column type's precision.
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.
> 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.
> 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.
> 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.
> 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:
> 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:
> INSERT INTO office_dogs (id, first_name, last_name) VALUES
(1, 'Petee', 'Hirata'),
(2, 'Carl', 'Kimball'),
(3, 'Ernie', 'Narayan');
> 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:
> 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:
> ALTER TABLE office_dogs ALTER COLUMN full_name DROP STORED;
Check that the computed column was converted:
> 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:
> INSERT INTO office_dogs (id, first_name, last_name, full_name) VALUES (4, 'Lola', 'McDog', 'This is not computed');
> 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)
Alter the formula for a computed column
To alter the formula for a computed column, you must DROP
and ADD
the column back with the new definition. Take the following table for instance:
> CREATE TABLE x (
a INT NULL,
b INT NULL AS (a * 2) STORED,
c INT NULL AS (a + 4) STORED,
FAMILY "primary" (a, b, rowid, c)
);
CREATE TABLE
Time: 4ms total (execution 4ms / network 0ms)
Add a computed column d
:
> ALTER TABLE x ADD COLUMN d INT AS (a // 2) STORED;
ALTER TABLE
Time: 199ms total (execution 199ms / network 0ms)
If you try to alter it, you'll get an error:
> ALTER TABLE x ALTER COLUMN d INT AS (a // 3) STORED;
invalid syntax: statement ignored: at or near "int": syntax error
SQLSTATE: 42601
DETAIL: source SQL:
ALTER TABLE x ALTER COLUMN d INT AS (a // 3) STORED
^
HINT: try \h ALTER TABLE
However, you can drop it and then add it with the new definition:
> SET sql_safe_updates = false;
> ALTER TABLE x DROP COLUMN d;
> ALTER TABLE x ADD COLUMN d INT AS (a // 3) STORED;
> SET sql_safe_updates = true;
SET
Time: 1ms total (execution 0ms / network 0ms)
ALTER TABLE
Time: 195ms total (execution 195ms / network 0ms)
ALTER TABLE
Time: 186ms total (execution 185ms / network 0ms)
SET
Time: 0ms total (execution 0ms / network 0ms)
Increase a column type's precision
The TPC-C database contains a customer
table with a column c_credit_lim
of type DECIMAL(10,2)
. Suppose you want to increase the precision of the column's data type to DECIMAL (12,2)
:
> ALTER TABLE customer ALTER c_credit_lim type DECIMAL (12,2);
ALTER TABLE
Time: 80.814044ms