In PostgreSQL, a view is a virtual table that is based on a SELECT query. A VIEW in Postgres enables us to define a SELECT statement as a named object, which can be used to query data just like a regular table. Postgres offers various commands to work with views, such as CREATE VIEW, DROP VIEW, etc. In Postgres, the “ALTER VIEW” and “CREATE OR REPLACE VIEW” statements are used to modify a view.

In this tutorial, we will show you how to alter a view in Postgres to modify the definition of a view. So, let’s start.

How to Use CREATE OR REPLACE VIEW Command in Postgres?

In Postgres, the “CREATE OR REPLACE VIEW” command is used to modify the view’s definition. Use the following syntax to use the “CREATE OR REPLACE VIEW” statement in Postgres:

CREATE OR REPLACE name_of_view
AS query;

The create or replace command will create a new view if it doesn’t exist already. And it will modify the view’s definition according to the specified query if it already exists.

Example: Modifying a VIEW

We have created a sample view named “emp_view,” whose data is shown in the following snippet:

We have also created a sample table whose structure is shown in the below snippet:

Suppose we want to modify the “emp_view” according to the “emp_bio” table. To do that, we will execute the CREATE OR REPLACE view statement as follows:

CREATE OR REPLACE VIEW emp_view AS
SELECT emp_id, emp_name, emp_salary, emp_age
FROM emp_bio;

To verify the modified view, execute the “SELECT *” command:

The output snippet proves that the targeted view has been modified successfully.

How to Use ALTER VIEW Command in Postgres?

The ALTER VIEW command allows us to alter/modify the view’s auxiliary properties. Using the ALTER VIEW statement assists us in setting a default column, renaming a view, etc.

Example: Renaming a View

We have already created a view named “example_view”, whose content is shown in the following snippet:

Suppose we want to rename a view from “example_view” to “sample_view”. For this purpose, we will use the ALTER VIEW statement as follows:

ALTER VIEW example_view
RENAME TO sample_view;

The output snippet shows that the “example_view” has been modified successfully.

That’s it from this post!

Conclusion

In Postgres, the “ALTER VIEW” and “CREATE OR REPLACE VIEW” statements are used to modify a view. In Postgres, the “CREATE OR REPLACE VIEW” command is used to modify the view’s definition. It creates a new view if it doesn’t exist already and modifies the view’s definition according to the specified query if it already exists. The ALTER VIEW command allows us to alter/modify the view’s auxiliary properties. This blog post explained different methods to modify a view in Postgres through practical examples.