On this page
The DROP COLUMN
statement is part of ALTER TABLE
and removes columns from a table.
Note:
The DROP COLUMN
statement performs a schema change. For more information about how online schema changes work in
CockroachDB, see Online Schema Changes.
Warning:
When used in an explicit transaction combined with other schema changes to the same table, DROP COLUMN
can result in data loss if one of the other schema changes fails or is canceled. To work around this, move the DROP COLUMN
statement to its own explicit transaction or run it in a single statement outside the existing transaction.
Note:
By default, DROP COLUMN
drops any 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.
Synopsis
ALTERTABLEIFEXISTStable_nameDROP COLUMNIFEXISTSnameCASCADERESTRICT
Required privileges
The user must have the CREATE
privilege on the table.
Parameters
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 |
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.
|
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:
icon/buttons/copy
Drop a column
If you no longer want a column in a table, you can drop it.
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 | | {primary} | false
address | VARCHAR | true | NULL | | {primary} | false
credit_card | VARCHAR | true | NULL | | {primary} | false
[5 rows]
If there is data in the table, the
sql_safe_updates
session variable must be set to false
.
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
icon/buttons/copy
> SET sql_safe_updates = false;
icon/buttons/copy
> ALTER TABLE users DROP COLUMN credit_card;
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 | | {primary} | false
address | VARCHAR | true | NULL | | {primary} | 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.
icon/buttons/copy
> CREATE VIEW expensive_rides AS SELECT id, city FROM rides WHERE revenue > 90;
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.
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]
icon/buttons/copy
> ALTER TABLE rides DROP COLUMN revenue CASCADE;
icon/buttons/copy
> SHOW CREATE expensive_rides;
ERROR: relation "expensive_rides" does not exist
SQLSTATE: 42P01
Drop an indexed column
DROP COLUMN
drops a column and any indexes on the column being dropped.
icon/buttons/copy
> CREATE INDEX start_end_idx ON rides[start_time, end_time];
icon/buttons/copy
> WITH x AS [SHOW INDEXES FROM rides] SELECT * FROM x 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]
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
icon/buttons/copy
> WITH x AS [SHOW INDEXES FROM rides] SELECT * FROM x WHERE index_name='start_end_idx';
table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+------------+------------+--------------+-------------+-----------+---------+-----------
[0 rows]
See also
DROP CONSTRAINT
DROP INDEX
ALTER TABLE
SHOW JOBS
- Online Schema Changes
Was this helpful?