PostgreSQL – Managing Views

Views in PostgreSQL are a powerful tool that allows you to simplify complex queries by abstracting them into virtual tables. They are derived from base tables or previously defined views, offering a convenient way to organize and manage your data without creating physical copies.

In this article, we will learn about views and managing views in PostgreSQL. We will introduce you to the concept of views, and show how views are created, modified, and removed. And most importantly we will also see how views can be implemented in DBMS.

A view in PostgreSQL can be defined as a virtual table that is derived from underlying base tables or previously defined views.

It is important to note the following points:

• Virtual Table: A view does not necessarily exist in its physical form i.e. its tuples are not always physically stored in the database.
• No Data Storage: Views do not store data themselves, which can limit certain update operations.
• Unlimited Querying: There are absolutely no limitations on querying a view.

Creating PostgreSQL Views

To specify a view, we use a create view statement.

Syntax:

CREATE [TEMP |TEMPORARY] VIEW view_name AS
SELECT column1, column2....
FROM table_name
WHERE [condition];

• The view is assigned a virtual table name, a list of attributes, and a query that defines its content.
• The ‘TEMP’ or ‘TEMPORARY’ option allows you to create a temporary view that exists only for the duration of the session.

Example: Consider the following tables:

BOOK TABLE

BOOK COPIES

Book_Authors

Now, from the following tables, suppose we wish to create a  view such that we can calculate the number of copies for each book given in the book table.

This can be done as follows:

CREATE VIEW BOOKCOUNT AS
SELECT Book_id,Title, Sum(No_of_copies) 
FROM BOOK A, BOOK_COPIES B
WHERE A.Book_id = B.Book_ID
GROUP BY A.BOOK_ID; 
SELECT * FROM BOOKCOUNT;

The output of the following code will be:

BOOKCOUNT

Updating PostgreSQL Views

We can change the defining query of a view, we use the CREATE VIEW statement with OR REPLACE addition as follows:

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

An update on a view is generally not feasible because it has some side effects on the underlying base tables. For example,if we update the sum of copies of a particular book we will have to update the corresponding number of book count in the bookcount table.

Therefore, we can say that:

• A view with a single defining table is updatable if the view attributes contain the primary key of the base relation as well as other attributes that do not have default values specified.
• Views that are defined by the natural join of multiple tables are not updatable.
• Views defined using grouping and aggregate functions are not updatable.

WITH CHECK OPTION

This clause can basically be added at the end of the view definition if the view has to be updated by the INSERT, UPDATE,DELETE statements.It will allow the system to reject operations that violate the SQL rules for view updates.

 In the given view BOOKCOUNT that we have created ,it is not updatable.

If we try to update BOOKCOUNT view we will get the following error:

Update BOOKCOUNT set title ='ADP' where Book_ID =1;

ERROR:The target table BOOKCOUNT is not updatable.

In fact any of the views that contain the following elements are not updatable:

• Aggregate functions such as MIN, MAX, SUM, AVG, and COUNT.
• DISTINCT
• GROUP BY clause.
• HAVING clause.
• UNION or UNION ALL clause.
• Left join or outer join.
• Sub query in the SELECT clause or in the WHERE clause that refers to the table appeared in the FROM clause.
• Reference to non-updatable view in the FROM clause.
• Reference only to literal values.
• Multiple references to any column of the base table

So we will create another view 

CREATE VIEW Example as
SELECT  title, Author_Name,
FROM BOOK, BOOK_AUTHORS
WHERE BOOK.Book_ID=BOOK_AUTHORS.Book_ID;

The created view will be:

Example

Now let’s update this view that we have created.

Updating PostgreSQL Views

UPDATE Example
SET Author_Name='Rahul'
WHERE Author_Name='Abhishek';

The updated view will look like this:

Example

Deleting PostgreSQL Views

To remove an existing view in PostgreSQL, we can  use DROP VIEW statement as follows:

DROP VIEW [ IF EXISTS ] view_name;

If a view name specified does not exist then obviously it would result in an error.So, in order to avoid facing errors we use the IF EXISTS option .

Example: To remove the view that we have created we will use the following command

DROP VIEW IF EXISTS BOOKCOUNT;

Let’s check the output for the same

After deleting the view ,when we try to retrieve the columns from the view , it gives an error saying that the referenced view doesn’t exist.