ADD COLUMN

On this page Carat arrow pointing down
Warning:
CockroachDB v19.1 is no longer supported. For more details, see the Release Support Policy.

The ADD COLUMN statement is part of ALTER TABLE and adds columns to tables.

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.

Synopsis

ALTER TABLE IF EXISTS table_name ADD COLUMN IF NOT EXISTS column_name typename col_qualification

Required privileges

The user must have the CREATE privilege on the table.

Parameters

Parameter Description
table_name The name of the table to which you want to add the column.
column_name The name of the column you want to add. The column name must follow these identifier rules and must be unique within the table but can have the same name as indexes or constraints.
typename The data type of the new column.
col_qualification An optional list of column definitions, which may include column-level constraints, collation, or column family assignments.

If the column family is not specified, the column will be added to the first column family. For more information about how column families are assigned, see Column Families.

Note that it is not possible to add a column with the foreign key constraint. As a workaround, you can add the column without the constraint, then use CREATE INDEX to index the column, and then use ADD CONSTRAINT to add the foreign key constraint to the column.

Viewing schema changes

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

Examples

Add a single column

icon/buttons/copy
> ALTER TABLE accounts ADD COLUMN names STRING;
icon/buttons/copy
> SHOW COLUMNS FROM accounts;
+-------------+-----------+-------------+----------------+-----------------------+-------------+
| column_name | data_type | is_nullable | column_default | generation_expression |   indices   |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
| id          | INT       |    false    | NULL           |                       | {"primary"} |
| balance     | DECIMAL   |    true     | NULL           |                       | {}          |
| names       | STRING    |    true     | NULL           |                       | {}          |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
(3 rows)

Add multiple columns

icon/buttons/copy
> ALTER TABLE accounts ADD COLUMN location STRING, ADD COLUMN amount DECIMAL;
icon/buttons/copy
> SHOW COLUMNS FROM accounts;
+-------------+-----------+-------------+----------------+-----------------------+-------------+
| column_name | data_type | is_nullable | column_default | generation_expression |   indices   |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
| id          | INT       |    false    | NULL           |                       | {"primary"} |
| balance     | DECIMAL   |    true     | NULL           |                       | {}          |
| names       | STRING    |    true     | NULL           |                       | {}          |
| location    | STRING    |    true     | NULL           |                       | {}          |
| amount      | DECIMAL   |    true     | NULL           |                       | {}          |
+-------------+-----------+-------------+----------------+-----------------------+-------------+
(5 rows)

Add a column with a NOT NULL constraint and a DEFAULT value

icon/buttons/copy
> ALTER TABLE accounts ADD COLUMN interest DECIMAL NOT NULL DEFAULT (DECIMAL '1.3');
icon/buttons/copy
> SHOW COLUMNS FROM accounts;
+-------------+-----------+-------------+------------------------+-----------------------+-------------+
| column_name | data_type | is_nullable |     column_default     | generation_expression |   indices   |
+-------------+-----------+-------------+------------------------+-----------------------+-------------+
| id          | INT       |    false    | NULL                   |                       | {"primary"} |
| balance     | DECIMAL   |    true     | NULL                   |                       | {}          |
| names       | STRING    |    true     | NULL                   |                       | {}          |
| location    | STRING    |    true     | NULL                   |                       | {}          |
| amount      | DECIMAL   |    true     | NULL                   |                       | {}          |
| interest    | DECIMAL   |    false    | 1.3:::DECIMAL::DECIMAL |                       | {}          |
+-------------+-----------+-------------+------------------------+-----------------------+-------------+
(6 rows)

Add a column with NOT NULL and UNIQUE constraints

icon/buttons/copy
> ALTER TABLE accounts ADD COLUMN cust_number DECIMAL UNIQUE NOT NULL;

Add a column with collation

icon/buttons/copy
> ALTER TABLE accounts ADD COLUMN more_names STRING COLLATE en;

Add a column and assign it to a column family

Add a column and assign it to a new column family

icon/buttons/copy
> ALTER TABLE accounts ADD COLUMN location1 STRING CREATE FAMILY new_family;

Add a column and assign it to an existing column family

icon/buttons/copy
> ALTER TABLE accounts ADD COLUMN location2 STRING FAMILY existing_family;

Add a column and create a new column family if column family does not exist

icon/buttons/copy
> ALTER TABLE accounts ADD COLUMN new_name STRING CREATE IF NOT EXISTS FAMILY f1;

See also


Yes No
On this page

Yes No