DROP VIEW

On this page Carat arrow pointing down
Warning:
CockroachDB v19.1 is no longer supported as of October 30, 2020. For more details, refer to the Release Support Policy.

The DROP VIEW statement removes a view from a database.

Note:

This statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Required privileges

The user must have the DROP privilege on the specified view(s). If CASCADE is used to drop dependent views, the user must have the DROP privilege on each dependent view as well.

Synopsis

DROP VIEW IF EXISTS table_name , CASCADE RESTRICT

Parameters

Parameter Description
IF EXISTS Drop the view if it exists; if it does not exist, do not return an error.
table_name A comma-separated list of view names. To find view names, use:

SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';
CASCADE Drop other views that depend on the view being dropped.

CASCADE does not list views it drops, so should be used cautiously.
RESTRICT (Default) Do not drop the view if other views depend on it.

Examples

Remove a view (no dependencies)

In this example, other views do not depend on the view being dropped.

icon/buttons/copy
> SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';
+---------------+-------------------+--------------------+------------+---------+
| TABLE_CATALOG |   TABLE_SCHEMA    |     TABLE_NAME     | TABLE_TYPE | VERSION |
+---------------+-------------------+--------------------+------------+---------+
| def           | bank              | user_accounts      | VIEW       |       1 |
| def           | bank              | user_emails        | VIEW       |       1 |
+---------------+-------------------+--------------------+------------+---------+
(2 rows)
icon/buttons/copy
> DROP VIEW bank.user_emails;
DROP VIEW
icon/buttons/copy
> SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';
+---------------+-------------------+--------------------+------------+---------+
| TABLE_CATALOG |   TABLE_SCHEMA    |     TABLE_NAME     | TABLE_TYPE | VERSION |
+---------------+-------------------+--------------------+------------+---------+
| def           | bank              | user_accounts      | VIEW       |       1 |
+---------------+-------------------+--------------------+------------+---------+
(1 row)

Remove a view (with dependencies)

In this example, another view depends on the view being dropped. Therefore, it's only possible to drop the view while simultaneously dropping the dependent view using CASCADE.

Warning:
CASCADE drops all dependent views without listing them, which can lead to inadvertent and difficult-to-recover losses. To avoid potential harm, we recommend dropping objects individually in most cases.
icon/buttons/copy
> SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';
+---------------+-------------------+--------------------+------------+---------+
| TABLE_CATALOG |   TABLE_SCHEMA    |     TABLE_NAME     | TABLE_TYPE | VERSION |
+---------------+-------------------+--------------------+------------+---------+
| def           | bank              | user_accounts      | VIEW       |       1 |
| def           | bank              | user_emails        | VIEW       |       1 |
+---------------+-------------------+--------------------+------------+---------+
(2 rows)
icon/buttons/copy
> DROP VIEW bank.user_accounts;
pq: cannot drop view "user_accounts" because view "user_emails" depends on it
icon/buttons/copy
> DROP VIEW bank.user_accounts CASCADE;
DROP VIEW
icon/buttons/copy
> SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';
+---------------+-------------------+--------------------+------------+---------+
| TABLE_CATALOG |   TABLE_SCHEMA    |     TABLE_NAME     | TABLE_TYPE | VERSION |
+---------------+-------------------+--------------------+------------+---------+
| def           | bank              | create_test        | VIEW       |       1 |
+---------------+-------------------+--------------------+------------+---------+
(1 row)

See also


Yes No
On this page

Yes No