The DROP COLUMN statement is part of ALTER TABLE and removes columns from a table.

Note:

New in v20.2: By default, DROP COLUMN drops any indexes indexes on the column being dropped, and any indexes that reference the column, including partial indexes with predicates that reference the column and indexes with STORING clauses that reference the column.

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 DROP COLUMN IF EXISTS name CASCADE RESTRICT

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 drop.
name The name of the column you want to drop.

When a column with a CHECK constraint is dropped, the CHECK constraint is also dropped.
CASCADE Drop the column even if objects (such as views) depend on it; drop the dependent objects, as well. CASCADE will drop a column with a foreign key constraint if it is the only column in the reference.

CASCADE does not list the objects it drops, so should be used cautiously.

New in v20.2: CASCADE is not required to drop an indexed column, or a column that is referenced by an index. By default, DROP COLUMN drops any indexes indexes on the column being dropped, and any indexes that reference the column, including partial indexes with predicates that reference the column and indexes with STORING clauses that reference the column.
RESTRICT (Default) Do not drop the column if any objects (such as views) depend on it.

Viewing schema changes

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

Examples

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:

copy
icon/buttons/copy
$ cockroach demo

Drop a column

If you no longer want a column in a table, you can drop it.

copy
icon/buttons/copy
> SHOW COLUMNS FROM users;
  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden
--------------+-----------+-------------+----------------+-----------------------+-----------+------------
  id          | UUID      |    false    | NULL           |                       | {primary} |   false
  city        | VARCHAR   |    false    | NULL           |                       | {primary} |   false
  name        | VARCHAR   |    true     | NULL           |                       | {}        |   false
  address     | VARCHAR   |    true     | NULL           |                       | {}        |   false
  credit_card | VARCHAR   |    true     | NULL           |                       | {}        |   false
(5 rows)

If there is data in the table, the sql_safe_updates session variable must be set to false.

copy
icon/buttons/copy
> ALTER TABLE users DROP COLUMN credit_card;
ERROR: rejected (sql_safe_updates = true): ALTER TABLE DROP COLUMN will remove all data in that column
SQLSTATE: 01000
copy
icon/buttons/copy
> SET sql_safe_updates = false;
copy
icon/buttons/copy
> ALTER TABLE users DROP COLUMN credit_card;
copy
icon/buttons/copy
> SHOW COLUMNS FROM users;
  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden
--------------+-----------+-------------+----------------+-----------------------+-----------+------------
  id          | UUID      |    false    | NULL           |                       | {primary} |   false
  city        | VARCHAR   |    false    | NULL           |                       | {primary} |   false
  name        | VARCHAR   |    true     | NULL           |                       | {}        |   false
  address     | VARCHAR   |    true     | NULL           |                       | {}        |   false
(4 rows)

Prevent dropping columns with dependent objects (RESTRICT)

If the column has dependent objects, such as views, CockroachDB will not drop the column by default. However, if you want to be sure of the behavior you can include the RESTRICT clause.

copy
icon/buttons/copy
> CREATE VIEW expensive_rides AS SELECT id, city FROM rides WHERE revenue > 90;
copy
icon/buttons/copy
> ALTER TABLE rides DROP COLUMN revenue RESTRICT;
ERROR: cannot drop column "revenue" because view "expensive_rides" depends on it
SQLSTATE: 2BP01
HINT: you can drop expensive_rides instead.

Drop a column and its dependent objects (CASCADE)

If you want to drop the column and all of its dependent options, include the CASCADE clause.

Warning:

CASCADE does not list objects it drops, so should be used cautiously.

copy
icon/buttons/copy
> SHOW CREATE expensive_rides;
    table_name    |                                              create_statement
------------------+-------------------------------------------------------------------------------------------------------------
  expensive_rides | CREATE VIEW public.expensive_rides (id, city) AS SELECT id, city FROM movr.public.rides WHERE revenue > 90
(1 row)
copy
icon/buttons/copy
> ALTER TABLE rides DROP COLUMN revenue CASCADE;
copy
icon/buttons/copy
> SHOW CREATE expensive_rides;
ERROR: relation "expensive_rides" does not exist
SQLSTATE: 42P01

Drop an indexed column

New in v20.2: DROP COLUMN drops a column and any indexes on the column being dropped.

copy
icon/buttons/copy
> CREATE INDEX start_end_idx ON rides(start_time, end_time);
copy
icon/buttons/copy
> SELECT * FROM [SHOW INDEXES FROM rides] WHERE index_name='start_end_idx';
  table_name |  index_name   | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+---------------+------------+--------------+-------------+-----------+---------+-----------
  rides      | start_end_idx |    true    |            1 | start_time  | ASC       |  false  |  false
  rides      | start_end_idx |    true    |            2 | end_time    | ASC       |  false  |  false
  rides      | start_end_idx |    true    |            3 | city        | ASC       |  false  |   true
  rides      | start_end_idx |    true    |            4 | id          | ASC       |  false  |   true
(4 rows)
copy
icon/buttons/copy
> ALTER TABLE rides DROP COLUMN start_time;
NOTICE: the data for dropped indexes is reclaimed asynchronously
HINT: The reclamation delay can be customized in the zone configuration for the table.
ALTER TABLE
copy
icon/buttons/copy
> SELECT * FROM [SHOW INDEXES FROM rides] WHERE index_name='start_end_idx';
  table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+------------+------------+--------------+-------------+-----------+---------+-----------
(0 rows)

See also



YesYes NoNo