Summary: in this tutorial, we will show you the requirements for updatable views and how to create updatable views in PostgreSQL.
A PostgreSQL view is updatable when it meets the following conditions:
- The defining query of the view must has exactly one entry in the
FROMclause, which can be a table or another updatable view. - The defining query must not contain one of the following clauses at top level: GROUP BY, HAVING, LIMIT, OFFSET, DISTINCT, WITH, UNION, INTERSECT, and EXCEPT.
- The selection list must not contain any window function or set-returning function or any aggregate function such as SUM, COUNT, AVG, MIN, MAX, etc.
An updatable view may contain both updatable and non-updatable columns. If you try to insert or update a non-updatable column, PostgreSQL will raise an error.
When you execute an update operation such as INSERT, UPDATE or DELETE, PosgreSQL will convert this statement into the corresponding statement of the underlying table.
In case you have a WHERE condition in the defining query of a view, you still can update or delete the rows that are not visible through the view. However, if you want to avoid this, you can use CHECK OPTION when you define the view.
Users who perform update operations must have corresponding privilege on the view, but they don’t need to have privilege on the underlying table. However, view owners must have relevant privilege of the underlying table.
PosgreSQL Updatable Views Example
First, we create a new updatable view name usa_cities using CREATE VIEW statement. This view contains cities in the city table that belongs to USA.
1 2 3 4 5 6 7 | CREATE VIEW usa_cities AS SELECT city, country_id FROM city WHERE country_id = 103; |
Next, we can check the data in the usa_cities view by executing a simple SELECT statement:
1 2 3 4 | SELECT * FROM usa_cities; |
Then, we insert a new city to the city table through the usa_cities view using the following INSERT statement:
1 2 | INSERT INTO usa_cities (city, country_id) VALUES('San Jose', 103); |
After that, we check the data in the city table.
1 2 3 4 5 6 7 8 9 | SELECT city, country_id FROM city WHERE country_id = 103 ORDER BY last_update DESC; |
We have a newly entry added to the city table.
Finally, we can delete the entry that we have added through the usa_cities view.
1 2 3 4 5 | DELETE FROM usa_cities WHERE city = 'San Jose'; |
The entry has been deleted from the city table through the usa_cities view.
In this tutorial, we have shown how to create PostgreSQL updatable views and introduced you to conditions that views must satisfy to become automatically updatable .