DROP VIEW - CockroachDB

The DROP VIEW removes a from a database.

The “ statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Required privileges

The user must have the DROP 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

Parameters

Parameter	Description
`MATERIALIZED`	Drop a .
`IF EXISTS`	Drop the view if it exists; if it does not exist, do not return an error.
`view_name_list`	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.

> SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';

  table_catalog | table_schema |  table_name   | table_type | is_insertable_into | version
----------------+--------------+---------------+------------+--------------------+----------
  bank          | public       | user_accounts | VIEW       | NO                 |       2
  bank          | public       | user_emails   | VIEW       | NO                 |       1
(2 rows)

> DROP VIEW bank.user_emails;

DROP VIEW

> SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';

  table_catalog | table_schema |  table_name   | table_type | is_insertable_into | version
----------------+--------------+---------------+------------+--------------------+----------
  bank          | public       | user_accounts | VIEW       | NO                 |       4
(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.

> SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';

  table_catalog | table_schema |  table_name   | table_type | is_insertable_into | version
----------------+--------------+---------------+------------+--------------------+----------
  bank          | public       | user_accounts | VIEW       | NO                 |       2
  bank          | public       | user_emails   | VIEW       | NO                 |       1
(2 rows)

> DROP VIEW bank.user_accounts;

ERROR: cannot drop relation "user_accounts" because view "user_emails" depends on it
SQLSTATE: 2BP01

> DROP VIEW bank.user_accounts CASCADE;

DROP VIEW

> SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';

  table_catalog | table_schema | table_name | table_type | is_insertable_into | version
----------------+--------------+------------+------------+--------------------+----------
(0 rows)

​Required privileges

​Synopsis

​Parameters

​Examples

​Remove a view (no dependencies)

​Remove a view (with dependencies)

​See also

Required privileges

Synopsis

Parameters

Examples

Remove a view (no dependencies)

Remove a view (with dependencies)

See also