PostgreSQL Updatable Views

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 FROM clause, 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.

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.

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.

PosgreSQL updatable views example

First, create a new updatable view name usa_cities using 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;
Code language: SQL (Structured Query Language) (sql)

Next, check the data in the usa_cities view by executing the following SELECT statement:

SELECT
	*
FROM
	usa_cities;
Code language: SQL (Structured Query Language) (sql)

Then, insert a new city to the city table through the usa_cities view using the following INSERT statement:

INSERT INTO usa_cities (city, country_id)
VALUES('San Jose', 103);
Code language: SQL (Structured Query Language) (sql)

After that, check the contents of the city table:

SELECT
	city,
	country_id
FROM
	city
WHERE
	country_id = 103
ORDER BY
	last_update DESC;
Code language: SQL (Structured Query Language) (sql)

We have a newly entry added to the city table.

PostgreSQL Updatable View - new city added

Finally, delete the entry that has been added through the usa_cities view.

DELETE
FROM
	usa_cities
WHERE
	city = 'San Jose';
Code language: SQL (Structured Query Language) (sql)

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

Was this tutorial helpful ?