PostgreSQL – Create updatable Views

A view can represent a subset of a real table, selecting certain columns or certain rows from an ordinary table. We can use them to restrict access to the original table so that the users can see only a sub-portion of a table. The table from which view is created is known as the base table. There can be updatable and non-updatable views. You can use updatable views in situations where you want certain users to update certain columns of specific tables. Any view is updatable if it meets the following requirements:

• There should be only one entry in the FROM clause of the defining query of the view
• The selection list must not contain any aggregate function such as SUM, MIN, MAX, etc.
• GROUP BY, HAVING, LIMIT, OFFSET, DISTINCT, WITH, UNION, INTERSECT, and EXCEPT statements can not be used in the query in View.

This would be more clear with the help of an example. Let us see the following example. Below is the table named “example” that holds some basic data about the employees:+

Original table

Syntax to create the updatable view

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Creating an updatable view

Now we can create a view from the original table “example”, A view can take one or more than one column in the selection list depending on how much access you want to give to your users. Let us create an updatable view “my_view” with three columns id, name, and dept respectively. 

CREATE OR REPLACE VIEW my_view AS
SELECT id,name,dept from example
WHERE dept='Sales';

Output:
CREATE VIEW
Query returned successfully in 220 msec.

Querying the results

We can simply see the results of the created view by simply running a SELECT query as follows:

SELECT * from my_view; 

Output:

my_view

Since there was a single row with dept as “Sales”, hence we got only a single row in the results.

Inserting in the created view

Now let us try to perform the INSERT operation in the created view using the following syntax and example as well.

SYNTAX:
INSERT INTO view_name (column1,column2,...columnN) VALUES(Val1, val2...valN);

EXAMPLE:
INSERT INTO my_view (id,name,dept)VALUES(106,'Johnson','Health');

Now, if we run the SELECT query, no change will be observed in the created view and this is due to the reason that the new dept we added while inserting the value is “Health” but while creating our view we have mentioned “Sales” in the WHERE clause, so in the filter, no changes would be observed. However, we can see a change (a new row added) in the original table.

So now, if we run the query as “SELECT * FROM example”, the output will be as follows:

Updated table

The two main changes that can be observed here are – “Null” value in the last column of the table and the total number of rows got incremented by one. This shows that we have successfully created an updatable view. Now let us see how we can make some updations in the view itself.

Updating the created view

We can also update the created view using the following syntax :

SYNTAX:
UPDATE view_name SET column = "New Value";

EXAMPLE:
UPDATE my_view SET dept = "Health";

OUTPUT:
UPDATE VIEW
Query returned successfully in 180 msec.

So now, if we run the query as “SELECT * FROM example”, the output will be as follows:

Final table

The main changes that can be observed here are – The row that previously held the dept value as “Sales” has been updated with the dept value as “Health”. Also, the order of row 2 has been changed, it has now been added in the last.

So this how we can create and perform operations on the updatable view.