PostgreSQL views make it easy to save and run SQL queries repeatedly, without having to write the query again and again. However, if you don’t need a PostgreSQL view, you can drop it from your database. Here’s how to drop view in PostgreSQL, using PostgreSQL DROP VIEW statement.
How to Drop View in PostgreSQL
Here are the steps to drop view in PostgreSQL using PostgreSQL DROP VIEW statement.
Here’s the syntax for DROP VIEW statement.
DROP VIEW [ IF EXISTS ] view_name;
In the above statement replace view_name with your view name.
If you drop view that doesn’t exist you will get an error. So you can optionally add IF EXISTS keyword to delete view only if it exists. Here’s an example
postgres=# drop view temp_view; ERROR: view "temp_view" does not exist postgres=# drop view if exists temp_view; NOTICE: view "temp_view" does not exist, skipping DROP VIEW
Bonus Read : How to List PostgreSQL Views
PostgreSQL DROP VIEW Example
Let’s say you have the following view
postgres=# create view sales_view as select * from sales; CREATE VIEW postgres=# select * from sales; order_date | sale ------------+------ 2020-04-01 | 210 2020-04-02 | 125 2020-04-03 | 150 2020-04-04 | 230 2020-04-05 | 200 2020-04-10 | 220 2020-04-06 | 25 2020-04-07 | 215 2020-04-08 | 300 2020-04-09 | 250
Bonus Read : How to Get First Row Per Group in PostgreSQL
Here’s the SQL query to drop view in PostgreSQL.
postgres=# drop view if exists sales_view; DROP VIEW postgres=# select * from sales_view; ERROR: relation "sales_view" does not exist LINE 1: select * from sales_view;
Bonus Read : How to Create View in PostgreSQL
PostgreSQL DROP VIEW Cascade
If you also want to delete PostgreSQL objects that depend on your view, then you need to use CASCADE keyword in your PostgreSQL DROP VIEW query.
Here’s the syntax
PostgreSQL DROP VIEW view_name CASCADE
In the above query, replace view_name with your view name.