PostgreSQL Tutorial: Updatable Views

Summary: In this tutorial, we will discuss the requirements for updatable views and show you how to create updatable views in PostgreSQL.

Table of Contents

Introduction to PostgreSQL updatable views

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.

PostgreSQL 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;

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

SELECT
	*
FROM
	usa_cities;

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);

After that, check the contents of the city table:

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, delete the entry that has been added through the usa_cities view.

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.