In Postgres, a view is a virtual table that can represent a subset of an ordinary table. It can be created based on a single or multiple tables. Views can be updatable or non-updatable. In Postgres, a view is referred to as an updatable view if it has at least one updatable column. Users can modify the records of original/base tables using updatable views.

This post will explain how to create and use an updatable view in Postgres using suitable examples.

How to Create Updatable Views in Postgres?

Any view that has at least one updatable column is referred to as an updatable view. Users must follow the following rules to create an updatable view in Postgres:

- An updatable view can have updatable as well as non-updatable columns. However, Non-updatable columns cannot be altered by users; if they attempt to, Postgres will issue an error.
- Users must specify only one table or an updatable view in the FROM clause of a query for a specific view.
- Users can’t specify a window, aggregate, or set-returning function in the selection list of a view.
- Users can’t specify the clauses like LIMIT, GROUP BY, OFFSET, HAVING, UNION, EXCEPT, INTERSECT, DISTINCT, and WITH at the top level.
- The view's invisible rows(rows that don’t meet the criteria specified in the WHERE clause) can also be modified. However, this rule can be bypassed by using the CHECK OPTION when defining the view.

Syntax

Utilize the following syntax to create an updatable view in Postgres:

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

The above query will create a new updatable view based on the specified table.

Example 1: Creating an Updatable View

We have created a sample table with the following records:

Suppose we want to create an updatable view based on the “employee_tab” view. To do this, we will use the following query:

CREATE OR REPLACE VIEW employee_view AS
SELECT id, emp_name,emp_age 
FROM employee_tab
WHERE emp_age <= 30;

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

SELECT * FROM employee_view;

All those employees with ages less than are equal to 30 are inserted into the “employee_view”. Let’s update the view by inserting a new record into it:

INSERT INTO employee_view(id, emp_name, emp_age)
VALUES (14, 'Joe', 26 );

The view has been successfully modified. You check the updated/inserted record using the following query:

SELECT * FROM employee_view;

The output proves that the insert operation is successfully performed on the given updatable view. The above-applied modifications will also be made to the base table since the given view is updatable. You can confirm the modification in the base table by executing the following command:

SELECT * FROM employee_tab;

From the output, you can observe that the base table has also been updated.

Conclusion

Any view that has at least one updatable column is referred to as an updatable view. An updatable view can have updatable as well as non-updatable columns. However, Non-updatable columns cannot be altered by users; if they attempt to, Postgres will issue an error. Moreover, users must specify only one table or an updatable view in the FROM clause of the view’s defining query. This post has illustrated a detailed procedure to create and use an updatable view in PostgreSQL.