Views are one of the handiest tools in PostgreSQL for simplifying complex queries and presenting data in a cleaner way. But views, like any other database object, sometimes need to be cleaned up. Maybe a view is no longer needed, maybe it’s been replaced by a better-designed one, or maybe it’s just cluttering up your schema. That’s where the DROP VIEW command comes in. In this article, I’ll cover everything from the basic syntax to advanced scenarios involving dependencies, cascading drops, and troubleshooting.
What Is a View, Briefly
Before diving into DROP VIEW, it helps to remember what a view actually is. A view is essentially a stored SQL query that you can treat like a virtual table. When you query a view, PostgreSQL runs the underlying query behind the scenes and returns the result. Views don’t store data themselves (unless you’re using a materialized view, which is a different concept), so removing a view doesn’t delete any actual data, it just removes the saved query definition.
Basic Syntax of DROP VIEW
The basic syntax looks like this:
DROP VIEW [IF EXISTS] view_name [, ...] [CASCADE | RESTRICT];
Here’s what each part means:
- IF EXISTS: prevents an error if the view doesn’t exist, which is great for scripts that need to run safely multiple times.
- view_name: the name of the view (or views, comma-separated) you want to remove.
- CASCADE: automatically drops any objects that depend on the view, such as other views built on top of it.
- RESTRICT: the default behavior, which prevents the drop if any other object depends on the view.
A Simple Example
Let’s say you created a view earlier to summarize monthly sales:
CREATE VIEW monthly_sales_summary AS
SELECT date_trunc('month', order_date) AS month, SUM(total_amount) AS total_sales
FROM orders
GROUP BY 1;
If you no longer need it, you can drop it like this:
DROP VIEW monthly_sales_summary;
That’s it. The view definition is removed, and any queries relying on it will now fail unless you recreate it.
Using IF EXISTS to Avoid Errors
If you’re not sure whether a view exists, or you’re writing a migration script that might run in different environments, use IF EXISTS:
DROP VIEW IF EXISTS monthly_sales_summary;
Without IF EXISTS, trying to drop a view that doesn’t exist throws an error like:
ERROR: view "monthly_sales_summary" does not exist
With IF EXISTS, PostgreSQL just prints a notice and moves on, which is much friendlier for automated scripts.
Dropping Multiple Views at Once
You can drop several views in a single statement by separating their names with commas:
DROP VIEW IF EXISTS monthly_sales_summary, yearly_sales_summary, quarterly_sales_summary;
This is convenient during cleanup operations when you’re removing a batch of related views at once, rather than writing separate statements for each.
Understanding CASCADE and RESTRICT
This is where DROP VIEW gets a little more nuanced, and where a lot of people run into trouble.
By default, PostgreSQL uses RESTRICT behavior, meaning it will refuse to drop a view if something else depends on it. For example, if you have a view called active_customers and another view called active_customers_with_orders that’s built on top of it, trying to drop active_customers without CASCADE will give you an error:
DROP VIEW active_customers;
ERROR: cannot drop view active_customers because other objects depend on it
DETAIL: view active_customers_with_orders depends on view active_customers
HINT: Use DROP ... CASCADE to drop the dependent objects too.
If you actually want to remove both the view and everything depending on it, you use CASCADE:
DROP VIEW active_customers CASCADE;
Be very careful with CASCADE. It doesn’t just warn you about dependent objects, it actually deletes them. If active_customers_with_orders was an important view that other parts of your application relied on, CASCADE will remove it silently as part of the operation, and you’ll only find out when something downstream breaks.
Checking Dependencies Before Dropping
Before running a CASCADE drop, it’s wise to check what actually depends on the view. You can query the system catalogs for this:
SELECT dependent_ns.nspname AS dependent_schema,
dependent_view.relname AS dependent_view
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_class AS source_view ON pg_depend.refobjid = source_view.oid
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
WHERE source_view.relname = 'active_customers';
This query lists any views (or other objects) that depend on active_customers, so you know exactly what will be affected before you decide whether to use CASCADE or handle each dependent object manually.
Dropping a View and Recreating It
A very common pattern in development is dropping and recreating a view when its definition changes. You can do this in two ways:
Option 1: Drop then create
DROP VIEW IF EXISTS monthly_sales_summary;
CREATE VIEW monthly_sales_summary AS
SELECT date_trunc('month', order_date) AS month, SUM(total_amount) AS total_sales
FROM orders
GROUP BY 1;
Option 2: CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW monthly_sales_summary AS
SELECT date_trunc('month', order_date) AS month, SUM(total_amount) AS total_sales, COUNT(*) AS order_count
FROM orders
GROUP BY 1;
The second option is often preferable because it preserves permissions granted on the view and doesn’t require dropping dependent objects, as long as you’re only adding columns and not removing or renaming existing ones. If you try to remove a column or change a column’s data type with CREATE OR REPLACE VIEW, PostgreSQL will throw an error, and you’ll need to actually drop and recreate the view instead.
Dropping Materialized Views
It’s worth noting that DROP VIEW only works on regular views. If you’re working with a materialized view (created with CREATE MATERIALIZED VIEW), you need a different command:
DROP MATERIALIZED VIEW IF EXISTS my_materialized_view;
Trying to use DROP VIEW on a materialized view will give you an error telling you it’s the wrong object type.
Permissions Required to Drop a View
To drop a view, you generally need to be the owner of the view, or be a superuser, or have been granted appropriate permissions. If you try to drop a view you don’t own and don’t have privileges for, you’ll see something like:
ERROR: must be owner of view monthly_sales_summary
If you need to drop a view owned by another role, you can either have that role drop it, ask a superuser to do it, or use ALTER VIEW ... OWNER TO to change ownership first (if you have permission to do that).
Common Use Cases for DROP VIEW
- Cleaning up during development: removing throwaway views you created for testing or exploration.
- Schema refactoring: replacing an old view structure with a redesigned one, especially when column changes make
CREATE OR REPLACE VIEWinsufficient. - Removing deprecated reporting views: as business requirements change, old reporting views become obsolete and should be cleaned up to avoid confusion.
- Migration scripts: dropping views as part of a versioned migration process before recreating them with updated logic.
- Reducing schema clutter: in large databases, unused views accumulate over time and make it harder to understand what’s actually in use.
Troubleshooting Common Issues
“View Does Not Exist” Error
Double check the spelling and the schema. If the view lives in a non-default schema, you need to qualify it:
DROP VIEW IF EXISTS reporting.monthly_sales_summary;
“Cannot Drop View Because Other Objects Depend On It”
This means you need to either use CASCADE (after checking what will be affected) or manually drop the dependent objects first in the correct order.
Accidentally Dropped an Important View
Since DROP VIEW doesn’t have a built-in undo, your best recovery option is to have the view’s CREATE statement saved somewhere, like in version control, a migration file, or a schema backup. This is a strong argument for always keeping your view definitions in source control rather than only in the live database.
Permission Denied
Make sure you’re connected as the view owner or a role with sufficient privileges, or ask someone with the right access to run the drop for you.
Best Practices for Using DROP VIEW
- Always check dependencies first: before using CASCADE, run a dependency check so you know exactly what else will be removed.
- Use IF EXISTS in scripts: this makes your migration and deployment scripts idempotent, meaning they can be run multiple times safely.
- Keep view definitions in version control: store your
CREATE VIEWstatements in your codebase so you can always recreate a view if it’s dropped by mistake. - Prefer CREATE OR REPLACE VIEW when possible: it’s generally safer and preserves grants, so reserve DROP VIEW for cases where you truly need to remove a view or make structural changes that replace can’t handle.
- Be cautious with CASCADE in production: consider testing the cascade behavior in a staging environment first, or manually reviewing and dropping dependent views one at a time for more control.
- Document why a view was dropped: especially in team environments, a quick note in your migration history about why a view was removed can save someone confusion months down the line.
DROP VIEW in Migration Frameworks
If you’re using a migration framework like Flyway, Liquibase, Sqitch, or a custom migration runner built into your application framework, DROP VIEW statements typically live inside “down” or “rollback” migration files, paired with the corresponding CREATE VIEW in the “up” migration. This lets you version your schema changes and roll them back cleanly if something goes wrong:
-- up_003_create_sales_views.sql
CREATE VIEW monthly_sales_summary AS
SELECT date_trunc('month', order_date) AS month, SUM(total_amount) AS total_sales
FROM orders
GROUP BY 1;
-- down_003_create_sales_views.sql
DROP VIEW IF EXISTS monthly_sales_summary;
Keeping this discipline, where every CREATE has a matching DROP in a rollback script, makes your schema changes much safer to test, deploy, and revert if needed.
Comparing DROP VIEW to TRUNCATE and DELETE
New PostgreSQL users sometimes get confused about the difference between removing a view and removing data. It’s worth being explicit about this distinction:
DROP VIEWremoves the saved query definition. No underlying data is touched at all, since views don’t store data themselves.DELETE FROM tableremoves rows from an actual table, but the table structure remains.TRUNCATE tablequickly removes all rows from a table, again leaving the structure intact.DROP TABLEremoves both the structure and the data of an actual table.
If your goal is just to get rid of a saved query shortcut and you’re worried about losing data, you can rest easy: dropping a view is a purely cosmetic operation from the perspective of your actual stored data.
Scripting Safe View Cleanup
When cleaning up a batch of unused views across a large schema, it helps to first generate a list of all views and their dependencies before touching anything:
SELECT schemaname, viewname
FROM pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, viewname;
From there, you can cross-reference against your application’s query logs or codebase to identify which views are actually still being used, and build a safe drop script for the rest. This kind of audit is worth doing periodically, since views tend to accumulate over the lifetime of a project, and it’s easy to lose track of which ones matter.
Handling Ownership Changes Before Dropping
In team environments, it’s common for a view to be owned by whoever happened to create it, which might be an individual developer’s personal account rather than a shared application role. Before you can drop a view owned by someone else, you either need that person to run the drop, need superuser access, or need ownership transferred to you or your role first:
ALTER VIEW monthly_sales_summary OWNER TO db_admin;
DROP VIEW monthly_sales_summary;
This is a common friction point in growing teams, so it’s worth establishing early that important, shared views should be owned by a dedicated application or admin role rather than an individual’s personal login, precisely to avoid this kind of blocker later on.
Cleaning Up Views Tied to Deprecated Reporting Tools
A very practical scenario worth mentioning: many organizations accumulate views created specifically to feed a particular BI tool or reporting dashboard. When that tool gets replaced, the views built for it often get forgotten and left behind. A good habit is tagging or naming such views clearly, like bi_tableau_monthly_summary, so that when the tool is eventually retired, it’s easy to search for and clean up every view associated with it:
SELECT viewname FROM pg_views WHERE viewname LIKE 'bi_tableau_%';
From there, a batch DROP VIEW statement, ideally wrapped in a transaction so you can roll back if something looks wrong, makes the cleanup straightforward:
BEGIN;
DROP VIEW IF EXISTS bi_tableau_monthly_summary, bi_tableau_quarterly_summary CASCADE;
-- review results, then COMMIT or ROLLBACK
COMMIT;
Wrapping destructive DDL operations in an explicit transaction like this gives you a chance to inspect the outcome (via subsequent SELECT queries against related tables) before finalizing the change, and to back out cleanly with ROLLBACK if something unexpected happened.
Frequently Asked Questions
Can I drop a view that’s referenced inside a function?
If a function references a view in its body, PostgreSQL generally does not track that as a hard dependency the way it does for other views, since function bodies aren’t parsed for dependencies in the same way. This means DROP VIEW might succeed even though a function still references it, and that function will simply fail at runtime the next time it’s called. Always check your codebase for references, not just database-level dependencies.
Will dropping a view free up disk space?
Not meaningfully, since views don’t store data. There might be a tiny amount of catalog space freed, but don’t expect any measurable difference in your database’s disk usage.
Is there a way to temporarily disable a view without dropping it?
Not directly, no. Views don’t have an enable/disable mechanism like triggers do. If you need to temporarily “turn off” a view, your options are to drop and later recreate it, or use REVOKE to remove access to it temporarily without actually deleting the definition.
What happens to permissions if I drop and recreate a view?
Permissions granted directly on the view are lost when you drop it and need to be re-granted after recreating it. This is one of the reasons CREATE OR REPLACE VIEW is often preferred over drop-and-recreate, since it preserves existing grants.
Wrapping Up
DROP VIEW is a straightforward command on the surface, but the real complexity lives in managing dependencies safely, especially once your database has layers of views built on top of other views. Get comfortable with checking dependencies before you drop anything, use IF EXISTS for safer scripting, and lean on CREATE OR REPLACE VIEW when you’re just updating logic rather than truly needing to remove an object. With those habits in place, you’ll rarely be caught off guard by a DROP VIEW statement.