Skip to content

Latest commit

 

History

History
93 lines (70 loc) · 3.54 KB

alter-view.md

File metadata and controls

93 lines (70 loc) · 3.54 KB
title summary toc
ALTER VIEW
The ALTER VIEW statement applies a schema change to a view.
true

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

{{site.data.alerts.callout_info}} ALTER VIEW ... RENAME TO can be used to move a view from one database to another, but it cannot be used to move a view from one schema to another. To change a views's schema, use SET SCHEMA.

Note that, in a future release, ALTER VIEW ... RENAME TO will be limited to changing the name of a view, and will not have to the ability to change a view's database. {{site.data.alerts.end}}

{% include {{{ page.version.version }}/misc/schema-change-stmt-note.md %}

Required privileges

The user must have the DROP privilege on the view and the CREATE privilege on the parent database.

Synopsis

{% include {{ page.version.version }}/sql/generated/diagrams/alter_view.html %}

Parameters

Parameter Description
MATERIALIZED 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

{% include copy-clipboard.html %}

> 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)

{% include copy-clipboard.html %}

> ALTER VIEW bank.user_emails RENAME TO bank.user_email_addresses;

{% include copy-clipboard.html %}

> RENAME VIEW

{% include copy-clipboard.html %}

> 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