ALTER VIEW

Description

Modifies the auxiliary attributes of a view. (To modify the query definition of a view, use CREATE OR REPLACE VIEW.)

Precautions

Only the view owner and users with the ALTER permission on the current view can run the ALTER VIEW command. System administrators automatically have this permission when separation of duties is disabled. The following are permission constraints depending on the attributes to be modified:

To modify the schema of a view, you need to be either the view owner or a system administrator and have the CREATE permission on the new schema. However, when separation of duties is enabled, system administrators are restricted from making this modification.
To modify the owner of a view, you need to be either the view owner or a system administrator. Additionally, you must be a member of the new owner's role and have the CREATE permission on the schema of the view. However, when separation of duties is enabled, system administrators are restricted from making this modification.
Do not change the type of a column in a view.

Syntax

Set the default value of a view column.

ALTER VIEW [ IF EXISTS ] view_name
    ALTER [ COLUMN ] column_name SET DEFAULT expression;

Remove the default value of a view column.

ALTER VIEW [ IF EXISTS ] view_name
    ALTER [ COLUMN ] column_name DROP DEFAULT;

Modify the owner of a view.

ALTER VIEW [ IF EXISTS ] view_name 
    OWNER TO new_owner;

Rename a view.

ALTER VIEW [ IF EXISTS ] view_name 
    RENAME TO new_name;

Set the schema of a view.

ALTER VIEW [ IF EXISTS ] view_name 
    SET SCHEMA new_schema;

Parameters

IF EXISTS
If this option is used, no error is generated when the view does not exist, and only a message is displayed.
view_name
Specifies the view name, which can be schema-qualified.

Value range: existing view names (character string).
column_name
Specifies the column name.

Value range: existing column names (character string).
SET/DROP DEFAULT
Sets or deletes the default value of a column. This parameter does not take effect.
new_owner
Sets a username for the new owner of a view. The username must meet the requirements outlined in •user_name.
new_name
Specifies the new view name.
new_schema
Specifies the new schema of the view.
expression
Specifies constants, functions, or SQL expressions.

Example

Rename a view.

-- Create the test_tbl table.
m_db=# CREATE TABLE test_tb1(col1 INT,col2 INT);
-- Create a view.
m_db=# CREATE VIEW abc AS SELECT * FROM test_tb1;
-- Rename the view.
m_db=# ALTER VIEW IF EXISTS abc RENAME TO test_v1;
-- Query a view.
m_db=# \dv
             List of relations
 Schema |  Name   | Type | Owner | Storage 
--------+---------+------+-------+---------
 public | test_v1 | view | omm   | 
(1 row)

Modify the owner of a view.

-- Create a user.
m_db=# CREATE ROLE role_test PASSWORD '********';
-- Modify the owner of a view.
m_db=# ALTER VIEW IF EXISTS test_v1 OWNER TO role_test;
-- Query the view information.
m_db=# \dv
               List of relations
 Schema |  Name   | Type |   Owner   | Storage 
--------+---------+------+-----------+---------
 public | test_v1 | view | role_test | 
(1 row)

Set the schema of a view.

-- Create a schema.
m_db=# CREATE SCHEMA tcpds;
-- Modify the schema of a view.
m_db=# ALTER VIEW test_v1 SET SCHEMA tcpds;
-- Query the view information.
m_db=# \dv tcpds.test_v1;
               List of relations
 Schema |  Name   | Type |   Owner   | Storage 
--------+---------+------+-----------+---------
 tcpds  | test_v1 | view | role_test | 
(1 row)