PostgreSQL – Managing Views
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.
Consider the following tables:
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:
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.
- 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:
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:
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.