Introduction to the PostgreSQL ALTER VIEW statement

TheALTER VIEW statement allows you to change various properties of a view.

If you want to change the view’s defining query, use theCREATE OR REPLACE VIEW statement.

Here’s the basic syntax of theALTER VIEW statement:

ALTER VIEW [IF EXISTS] view_nameaction;

In this syntax:

First, specify the name of the view that you want to change in theALTER VIEW clause.

Second, use theIF EXISTS option to modify the view only if it exists. The statement will issue an error if you don’t use theIF EXISTS and attempt to change a non-existing view. But when you use theIF EXISTS, the statement issues a notice instead. TheIF EXISTS is optional.

Third, provide the action that you want to do with the view. The action includes renaming the view, setting the view option, and so on.

Renaming a view

The followingALTER VIEW statement changes the name of a view to the new one:

ALTER VIEW [ IF EXISTS ] view_nameRENAMETO new_view_name;

In this syntax, you specify the new view name (new_view_name) after theRENAME TO clause. For example:

First, create a new view calledfilm_type that includes thetitle andrating:

CREATE VIEW film_typeASSELECT title, ratingFROM film;

Second, change the viewfilm_type tofilm_rating:

ALTER VIEW film_type RENAMETO film_rating;

Changing the view option

The followingALTER VIEW statement changes the view option:

ALTER VIEW [ IF EXISTS ] view_nameSET ( view_option_name [= view_option_value] [, ... ] );

Theview_option_name can be:

• check_option: change the check option. The valid value islocal orcascaded.
• security_barrier: change the security-barrier property of a view. The valid value istrue orfalse.
• security_invoker: change the security invoker of a view. The valid value istrue orfalse.

For example, the following changes the check option of thefilm_rating view tolocal:

ALTER VIEW film_ratingSET (check_option= local);

To view the change, you can use the\d+ command inpsql:

\d+ film_rating

Output:

View "public.film_rating" Column |          Type          | Collation | Nullable | Default | Storage  | Description--------+------------------------+-----------+----------+---------+----------+------------- title  | character varying(255) |           |          |         | extended | rating | mpaa_rating            |           |          |         | plain    |View definition: SELECT title,    rating   FROM film;Options: check_option=local

Changing the view column

The following statement changes a column name of a view to a new one:

ALTER VIEW [ IF EXISTS ] view_nameRENAME [ COLUMN ] column_nameTO new_column_name;

For example, the following statement changes thetitle column of thefilm_rating view tofilm_title:

ALTER VIEW film_ratingRENAME titleTO film_title;

Here’s the new view detail:

\d+ film_rating

View "public.film_rating"   Column   |          Type          | Collation | Nullable | Default | Storage  | Description------------+------------------------+-----------+----------+---------+----------+------------- film_title | character varying(255) |           |          |         | extended | rating     | mpaa_rating            |           |          |         | plain    |View definition: SELECT title AS film_title,    rating   FROM film;Options: check_option=local

Setting the new schema

The following statement sets the new schema for a view:

ALTER VIEW [ IF EXISTS ] view_nameSET SCHEMA new_schema;

For example:

First, create a new schema calledweb:

CREATE SCHEMA web;

Second, change the schema of thefilm_rating view toweb:

ALTER VIEW film_ratingSET SCHEMA web;

Third, verify the change (inpsql):

\d+ web.film_rating

Output:

View "web.film_rating"   Column   |          Type          | Collation | Nullable | Default | Storage  | Description------------+------------------------+-----------+----------+---------+----------+------------- film_title | character varying(255) |           |          |         | extended | rating     | mpaa_rating            |           |          |         | plain    |View definition: SELECT title AS film_title,    rating   FROM film;Options: check_option=local

Summary

• Use theALTER VIEW ... RENAME TO statement to rename a view.
• Use theALTER VIEW ... (SET check_option) statement to change the check option of a view.
• Use theALTER VIEW ... SET SCHEMA statement to change the schema of a view.

Last updated onMarch 16, 2024

On this page

• Introduction to the PostgreSQL ALTER VIEW statement
• Summary