Open In App

PostgreSQL – Managing Views

Improve
Improve
Like Article
Like
Save
Share
Report

In this article, we will learn about views and managing views in PostgreSQL. We will introduce you to the concept of views, 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:

  • A view does not necessarily exist in its physical form i.e. its tuples are not always physically stored in the database.
  • This limits the update operations that can be applied to views.
  • 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 given a virtual table name, a list of attribute names, and a query to specify the contents of the view.

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_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.

Example for updating 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.

https://www.geeksforgeeks.org/postgresql-recursive-query/


Last Updated : 15 May, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads