The ALTER VIEW statement applies a schema change to a view.

Note:

This page only documents changing the name of a view. For information about changing the schema of a view, see SET SCHEMA.

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 view and the CREATE privilege on the parent database.

Synopsis

ALTER MATERIALIZED VIEW IF EXISTS view_name RENAME TO view_name

Parameters

Parameter Description
MATERIALIZED New in v20.2: Rename a materialized view.
IF EXISTS Rename the view only if a view of view_name exists; if one does not exist, do not return an error.
view_name The name of the view to rename. To find view names, use:

SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';
name The new name for the view, which must be unique to its database and follow these identifier rules.

Limitations

CockroachDB does not currently support:

  • Changing the SELECT statement executed by a view. Instead, you must drop the existing view and create a new view.
  • Renaming a view that other views depend on. This feature may be added in the future (see tracking issue).

Example

copy
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       |       2 |
| def           | bank              | user_emails        | VIEW       |       1 |
+---------------+-------------------+--------------------+------------+---------+
(2 rows)
copy
icon/buttons/copy
> ALTER VIEW bank.user_emails RENAME TO bank.user_email_addresses;
copy
icon/buttons/copy
> RENAME VIEW
copy
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       |       2 |
| def           | bank              | user_email_addresses | VIEW       |       3 |
+---------------+-------------------+----------------------+------------+---------+
(2 rows)

See also



Yes No