# PostgreSQL ALTER VIEW Statement With Examples

> In Postgres, the ALTER VIEW command modifies the views’ definition, such as setting the column's default value, changing the view’s owner, renaming a view, etc…

In Postgres, the **ALTER VIEW** statement is used to modify/alter the views’ definition. It allows us to modify the auxiliary properties of a view. Using the **ALTER VIEW** statement, you can set or drop the default value of a column, change the view’s owner, rename a view, etc. However, to execute the **ALTER VIEW** command, you must own the targeted view.

This blog illustrates several examples to showcase the usage of the Postgres ALTER VIEW statement. For this purpose, the below-mentioned topics will be covered in this blog:

  * How to Rename a VIEW?
  * How to Rename the VIEW’s Columns?
  * How to Change the VIEW’s Owner?
  * How to Set Default Value for a VIEW’s Column?
  * How to Remove Default Value From a VIEW’s Column?



 **How to Rename a VIEW?**

Let’s execute the “\dv” command to describe the list of views:
    
    
    \dv;

Suppose we want to rename the “staff_view” to “emp_view”. To do that, we use the “ALTER VIEW” statement as follows:
    
    
    ALTER VIEW staff_view
    RENAME TO emp_view;

Let’s execute the “\dv” command to get the list of available views:
    
    
    \dv;

The output signifies that the “staff_view” has been renamed “emp_view”.

 **How to Rename the VIEW’s Columns?**

Let’s execute the “\d” command followed by the view’s name to see the view’s columns:
    
    
    \d emp_view;

Suppose we need to rename the “staff_name” column to “emp_name”. For this, use the “ALTER VIEW” command with the “RENAME COLUMN” clause:
    
    
    ALTER VIEW emp_view
    RENAME COLUMN staff_name TO emp_name;

Let’s check the view’s columns using the “\d” command:
    
    
    \d emp_view;

The output snippet clarifies that the “staff_name” column has been renamed “emp_name”.

 **How to Change the VIEW’s Owner?**

Let’s execute the “\dv” command followed by the view’s name to describe the view’s details:
    
    
    \dv emp_view;

The above snippet shows that the owner of the “emp_view” is “postgres”. Let’s change it to “cp_user”:
    
    
    ALTER VIEW emp_view
    OWNER TO cp_user;

Specify the name of the user/role in place of “cp_user”:

Let’s execute the “\dv” command followed by the view’s name to check the view’s owner:
    
    
    \dv emp_view;

The output proves that the owner of the “emp_view” has been changed to “cp_user”.

 **How to Set Default Value for a VIEW’s Column?**

Firstly, let’s check the view’s columns using the “\d” command:
    
    
    \d emp_view;

Suppose we want to set the default value of the “staff_designation” column as “author”. For this, we will use the ALTER VIEW command as follows:
    
    
    ALTER VIEW emp_view
    ALTER COLUMN emp_designation SET DEFAULT;

Execute the “\d” command to see the columns’ definition:
    
    
    \d emp_view;

The output shows that a default value has been assigned to the “staff_designation” column.

 **How to Remove Default Value From a VIEW’s Column?**

Use the “ALTER VIEW” and “ALTER COLUMN” commands with the “DROP DEFAULT” clause to remove a default value from a view’s column:
    
    
    ALTER VIEW emp_view
    ALTER COLUMN staff_designation DROP DEFAULT;

Execute the “\d” command to see the columns’ definition:
    
    
    \d emp_view;

The default value has been successfully removed from the “staff_designation” column.

 **Conclusion**

In PostgreSQL, the **ALTER VIEW** command allows us to modify/alter the views’ definition. For instance, using the **ALTER VIEW** statement, you can set/drop the default value of a column, change the view’s owner, rename a view, etc. In this blog, we have demonstrated multiple examples to explain the usage of the Postgres ALTER VIEW statement.

---
[View this page online](https://www.commandprompt.com/education/postgresql-alter-view-statement-with-examples/)