August 3, 2023
Summary: in this tutorial, we will discuss the requirements for updatable views and show you 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 have 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 the top level: GROUP BY, HAVING, LIMIT, OFFSET, DISTINCT, WITH, UNION, INTERSECT, and EXCEPT.
- The selection list must not contain any window function, any set-returning function, or any aggregate function such as SUM, COUNT, AVG, MIN, and MAX.
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.
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.
When you perform update operations, you must have corresponding privilege on the view, but you don’t need to have privilege on the underlying table. However, view owners must have the relevant privilege of the underlying table.
PostgreSQL updatable views example
First, create a new updatable view name
CREATE VIEW statement. This view contains all cities in the
city table locating in the USA whose country id is 103.
CREATE VIEW usa_cities AS SELECT city, country_id FROM city WHERE country_id = 103;
Next, check the data in the
usa_cities view by executing the following
SELECT * FROM usa_cities;
Then, insert a new city to the
city table through the
usa_cities view using the following
INSERT INTO usa_cities (city, country_id) VALUES('San Jose', 103);
After that, check the contents of the
SELECT city, country_id FROM city WHERE country_id = 103 ORDER BY last_update DESC;
We have a newly entry added to the
Finally, delete the entry that has been added through the
DELETE FROM usa_cities WHERE city = 'San Jose';
The entry has been deleted from the
city table through the
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.