Postgres provides a “CREATE OR REPLACE VIEW” statement to create a new view or replace an existing view's defining query. It allows us to modify the defining query of a view without dropping a view. It defines a new view if the desired view doesn’t exist already or modifies the view’s defining query if it already exists.

This blog explains how to replace the view’s definition using the “CREATE or REPLACE VIEW” statement in Postgres.

How to Modify Views Defining Query?

Use the “OR REPLACE” parameter with the CREATE VIEW statement to alter the defining query of a view in Postgres:

CREATE OR REPLACE VIEW viewName AS
SELECT col_list
FROM tab_name
[WHERE condition];

Let’s comprehend the above syntax line-by-line:

- The CREATE OR REPLACE statement will create a new view if it doesn’t exist already. While it will modify the defining query of a view if the selected view already exists.
- Specify the columns to be added to the view in place of “col_list”.
- tab_name represents a table based on which the view will be created.
- WHERE Clause is optional that specifies a particular condition to filter the data.

Example 1: Create a New View

The following figure illustrates the sample table named “employees_details”:

SELECT * FROM employees_details;

Now execute the following statement to create a view from the “employees_details” table:

CREATE OR REPLACE VIEW employees_view AS
SELECT * FROM employees_details;

Run the below-provided command to verify the view’s creation:

SELECT * FROM employees_view;

The output shows that a view named “employees_view” has been successfully created.

Example 2: Modifying View’s Defining Query

Suppose we want to replace the view’s defining query, i.e., add only those employees to the view whose experience is more than three years. For this, use the following piece of code:

CREATE OR REPLACE VIEW employees_view AS
SELECT * FROM employees_details
WHERE emp_experience is > ‘3 Years’;

The above snippet uses the “OR REPLACE” parameter with the “CREATE VIEW” statement to modify the view’s defining query. The WHERE clause is used to filter the employees whose experience is more than three years:

The “CREATE VIEW” message in the output wind shows that the selected view has been replaced/modified. You can verify the view’s content via the “SELECT *” command:

SELECT * FROM employees_view;

This way, you can modify the view’s defining query in Postgres using the “CREATE OR REPLACE VIEW” statement.

Conclusion

Use the “OR REPLACE” parameter with the “CREATE VIEW” statement to alter/modify the defining query of a view in Postgres. Trying to modify the view’s defining query without using the “OR REPLACE” statement will throw a “relation already exists” error. This Postgre blog presented several examples to illustrate how to modify/replace the view’s definition using the “CREATE OR REPLACE” statement in Postgres.