ALTER VIEW

2020-02-16

The ALTER VIEW statement changes the name of a view.

{{site.data.alerts.callout_info}} It is not currently possible to change the SELECT statement executed by a view. Instead, you must drop the existing view and create a new view. Also, it is not currently possible to rename a view that other views depend on, but this ability may be added in the future (see this issue). {{site.data.alerts.end}}

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/diagrams/alter_view.html %}

Parameters

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

Example

> 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)
> ALTER VIEW bank.user_emails RENAME TO bank.user_email_addresses;
> RENAME VIEW
> 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