PostgreSQL – DROP VIEWS Statement

A view can be seen as a virtual table that can contain all rows of a table or selected rows from one or more tables. These allow us to see limited data only instead of complete information stored in a table. A view can be created from one or many Base Tables (the table from which view is created). We can perform various operations on views such as creating, updating and deleting views. Here, we’ll be focusing primarily on dropping or deleting views. Let us try to understand it with the help of an example as shown below : 

To understand the DROP VIEW statement, we’ll take a sample table named “Students” as shown here:

Students table

DROP VIEW Statement:

As the name suggests, The DROP VIEW statement removes a view from the database. The basic DROP VIEW syntax is as follows −

Syntax:
DROP VIEW [IF EXISTS] view_name
[CASCADE | RESTRICT]

Now let us understand the meaning of the keywords used in the above syntax one by one:

1. DROP VIEW keywords are used to specify the name of the view that needs to be dropped.
2. As the name suggests, IF EXISTS ensures that a drop is dropped only if it exists. If we attempt to drop a view that does not exist, then an error will be used.
3. view_name is simply the name of the view that is to be dropped.
4. RESTRICT does not allow the view to be deleted if any other objects are depending on it. It is the default option.
5. CASCADE automatically drops objects that depend on view and all objects that depend on those objects.

Note: It is also possible to delete multiple views using the single DROP VIEW  statement using the following syntax:

DROP VIEW [IF EXISTS] view_name1, view_name2, ...;

Now to understand the DROP VIEW statement, we need to first create a view that we can drop/delete later. Below is the statement that creates a view named “StudentsView” based on the information stored in the table “Students”.

CREATE OR REPLACE VIEW StudentsView
AS 
SELECT 
   admit_id,
   stu_name,
   branch 
FROM
Students
WHERE branch ='Science';

Output :  

StudentsView

So we have successfully created a view that contains three columns (admit_id, stu_name, branch)from the original table. Let us create one more view named “myview” that is based on the StudentsView using the following statement:

CREATE OR REPLACE VIEW myview 
AS 
SELECT 
   admit_id,
   stu_name 
FROM 
StudentsView 
WHERE stu_name ='Sakshi';

Output : 

myview

Since this view has been created from the first view named “StudentsView”, hence other objects depend on “StudentsView”. Now let us try to drop the view “StudentsView” using the DROP VIEW statement as shown here:

DROP VIEW StudentsView;

Output:

ERROR:  cannot drop view studentsview because other objects depend on it
DETAIL:  view myview depends on view studentsview
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Since RESTRICT was the default option hence it will not allow the view to be deleted if any other objects are depending on it and hence an error has been raised here. However, if we try to drop the myview, it can be simply dropped.

DROP VIEW myview;

Output:
DROP VIEW
Query returned successfully in 73 msec.

So this how we can create and drop views in PostgreSQL. To check whether the view has been successfully dropped or not, simply run the SELECT * FROM view_name if it still exists or has been dropped. In this case, if we run the below Query:

SELECT * from myview

then an error will be raised showing that myview does not exist as we have already dropped “myview“.