PostgreSQL Drop View

Summary: in this tutorial, you will learn how to use the PostgreSQL DROP VIEW statement to remove one or more views from the database.

Introduction to PostgreSQL DROP VIEW statement

The DROP VIEW statement removes a view from the database. The following illustrates the syntax of the DROP VIEW statement:

DROP VIEW [IF EXISTS] view_name [CASCADE | RESTRICT]
Code language: CSS (css)

In this syntax:

  • First, specify the name of the view after the DROP VIEW keywords.
  • Second, use the IF EXISTS option to drop a view only if it exists. If you don’t use the IF EXISTS option and drop a view that does not exist, PostgreSQL will issue an error. However, if you use the IF EXISTS option, PostgreSQL issues a notice instead.
  • Third, use the RESTRICT option to reject the removal of the view if there are any objects depending on it. The RESTRICT option is the default. If you use the CASCADE option, the DROP VIEW automatically drops objects that depend on view and all objects that depend on those objects.

To remove multiple views using a single statement, you specify a comma-separated list of view names after the DROP VIEW keywords like this:

DROP VIEW [IF EXISTS] view_name1, view_name2, ...;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To execute the DROP VIEW statement, you must be the owner of the view.

PostgreSQL DROP VIEW statement examples

See the following film, film_category, and category tables from the sample database:

film film_category category tables

Let’s create new views for practising.

The following statement creates a view based on the information from those tables:

CREATE VIEW film_master AS SELECT film_id, title, release_year, length, name category FROM film INNER JOIN film_category USING (film_id) INNER JOIN category USING(category_id);
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement creates a view called horror_film based on the film_master view:

CREATE VIEW horror_film AS SELECT film_id, title, release_year, length FROM film_master WHERE category = 'Horror';
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

And the following statement creates also a view called comedy_film based on the film_master view:

CREATE VIEW comedy_film AS SELECT film_id, title, release_year, length FROM film_master WHERE category = 'Comedy';
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

CREATE VIEW film_category_stat AS SELECT name, COUNT(film_id) FROM category INNER JOIN film_category USING (category_id) INNER JOIN film USING (film_id) GROUP BY name;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

CREATE VIEW film_length_stat AS SELECT name, SUM(length) film_length FROM category INNER JOIN film_category USING (category_id) INNER JOIN film USING (film_id) GROUP BY name;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

1) Using PostgreSQL DROP VIEW to drop one view

The following example uses the DROP VIEW statement to drop the comedy_film view:

DROP VIEW comedy_film;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

The following statement uses the DROP VIEW statement to drop the film_master view:

DROP VIEW film_master;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued an error:

ERROR: cannot drop view film_master because other objects depend on it DETAIL: view horror_film depends on view film_master HINT: Use DROP ... CASCADE to drop the dependent objects too. SQL state: 2BP01
Code language: Shell Session (shell)

The film_master has a dependent object which is the view horror_film.

To drop the view film_master, you need to drop its dependent object first or use the CASCADE option like this:

DROP VIEW film_master CASCADE;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

NOTICE: drop cascades to view horror_film
Code language: HTTP (http)

3) Using PostgreSQL DROP VIEW to drop multiple views

The following statement uses a single DROP VIEW statement to drop multiple views:

DROP VIEW film_length_stat, film_category_stat;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary

  • Use the DROP VIEW statement to remove one or more views from the database.
  • Use the IF EXISTS option to remove a view if it exists.
  • Use the CASCADE option to remove a view and its dependent objects recursively.
Was this tutorial helpful ?