In PostgreSQL, a view is a named query that helps us manipulate and retrieve data quickly. It can be created based on single or multiple tables. Views are helpful for wrapping a complex query, retrieving data quickly, etc. In Postgres, a new view can be created using the CREATE VIEW statement while an already created view can be updated using the UPDATE command.

This post will illustrate a practical guide on updating a View in PostgreSQL.

How to Update a View in Postgres?

Postgres UPDATE query can be utilized to modify or alter an updatable view. More specifically, you need to use the UPDATE query followed by the view name and then specify the column to be updated in the SET clause:

UPDATE view_name
SET col_name = modified_value;

Follow the provided steps to learn how to update a Postgres view:

Step 1: Sample Table

We have created a sample table with the following records:

Step 2: Create a View

Use the below query to define a new view based on the “employee_tab” table:

CREATE OR REPLACE VIEW employee_view_1 AS
SELECT id, emp_name,emp_age 
FROM employee_tab;

Use the “SELECT” query to fetch the data from the newly created view:

SELECT * FROM employee_view;

Step 3: Update View

Use the update query along with the SET clause to update the “employee_view_1”:

UPDATE employee_view_1
SET emp_age = 25;

Step 4: Verify Updated Data

Let’s fetch the view’s data using the following “SELECT” command:

SELECT * FROM employee_view_1;

The output snippet authenticates that the given view has been updated.

Conclusion

In PostgreSQL, an already created view can be altered or updated using the UPDATE command. For this purpose, you need to utilize the UPDATE query followed by the view name and then specify the column to be updated in the SET clause. This post has illustrated the step-by-step guide on how to update a view in PostgreSQL.