Introduction to PostgreSQL DROP VIEW statement

TheDROP VIEW statement allows you to remove a view from the database.

Here’s the basic syntax of theDROP VIEW statement:

DROP VIEW [IFEXISTS] view_name[CASCADE| RESTRICT];

In this syntax:

• First, specify the name of the view in theDROP VIEW clause.
• Second, useIF EXISTS to prevent an error if the view does not exist. PostgreSQL will issue a notice instead of an error when you attempt to remove a non-existing view. TheIF EXISTS is optional.
• Third, useCASCADE option to remove dependent objects along with the view or theRESTRICT option to reject the removal of the view if other objects depend on the view. TheRESTRICT option is the default.

Dropping multiple views

To drop multiple views simultaneously, you specify the view names separated by commas after theDROP VIEW keywords:

DROP VIEW [IF EXISTS] view_name1, view_name2, ...[CASCADE | RESTRICT];

Permissions

To execute theDROP VIEW statement, you need to be the owner of the view or have aDROP privilege on it.

PostgreSQL DROP VIEW statement examples

We’ll use the following tablesfilm,film_category, andcategory from thesample database:

Creating views for practicing

The following statement creates a new view calledfilm_info based on thefilm,film_category, andcategory tables:

CREATE VIEW film_info ASSELECT  film_id,  title,  release_year,  length,  name categoryFROM  film  INNER JOIN film_category USING (film_id)  INNER JOIN category USING(category_id);

The following statement creates a view calledhorror_film based on thefilm_info view:

CREATE VIEW horror_film ASSELECT  film_id,  title,  release_year,  lengthFROM  film_infoWHERE  category= 'Horror';

The following statement creates a view calledcomedy_film based on thefilm_master view:

CREATE VIEW comedy_film ASSELECT  film_id,  title,  release_year,  lengthFROM  film_infoWHERE  category= 'Comedy';

The following statement creates a view calledfilm_category_stat that returns the number of films by category:

CREATE VIEW film_category_stat ASSELECT  name,  COUNT(film_id)FROM  category  INNER JOIN film_categoryUSING (category_id)  INNER JOIN filmUSING (film_id)GROUP BY  name;

The following creates a view calledfilm_length_stat that returns the total length of films for each category:

CREATE VIEW film_length_stat ASSELECT  name,  SUM(length) film_lengthFROM  category  INNER JOIN film_categoryUSING (category_id)  INNER JOIN filmUSING (film_id)GROUP BY  name;

1) Using the DROP VIEW statement to drop one view example

The following example uses theDROP VIEW statement to drop thecomedy_film view:

DROP VIEW comedy_film;

2) Using the DROP VIEW statement to drop a view that has dependent objects

The following statement uses theDROP VIEW statement to drop thefilm_info view:

DROP VIEW film_info;

PostgreSQL issued an error:

ERROR:  cannotdrop view film_info because other objects dependon itDETAIL:  view horror_film dependson view film_infoHINT:Use DROP ... CASCADEto drop thedependent objects too.

Thefilm_info has a dependent object which is the viewhorror_film.

To drop the viewfilm_info, you need to drop its dependent object first or use theCASCADE option like this:

DROP VIEW film_infoCASCADE;

This statement drops thefilm_info view as well as its dependent object which is thehorror_film. It issued the following notice:

NOTICE:drop cascadesto view horror_film

3) Using the DROP VIEW statement to drop multiple views

The following statement uses a singleDROP VIEW statement to drop multiple views:

DROP VIEW film_length_stat, film_category_stat;

Summary

• Use theDROP VIEW statement to remove one or more views from the database.
• Use theIF EXISTS option to remove a view if it exists.
• Use theCASCADE option to remove a view and its dependent objects recursively.

Last updated onMarch 16, 2024

On this page

• Introduction to PostgreSQL DROP VIEW statement
• PostgreSQL DROP VIEW statement examples
• Summary