(opens in a new tab)

Postgres DROP TABLE IF EXISTS and CASCADE Guide

September 11, 2026 by Chat2DB Team

DROP TABLE looks like the simplest statement in SQL, but in PostgreSQL it interacts with dependency tracking, transactions, locks, ownership, and partitioning. Getting one of those wrong means either a failed deployment script or, worse, a view or constraint silently removed by CASCADE. This guide covers each behaviour with runnable examples so you know exactly what a Postgres drop table statement will and will not do.

Sample schema

All examples use three tables, a view, and a foreign key. Run this in a scratch database.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name        TEXT NOT NULL
);
 
CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers (customer_id),
    amount      NUMERIC(10,2) NOT NULL
);
 
CREATE TABLE order_items (
    item_id  SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders (order_id),
    sku      TEXT NOT NULL,
    qty      INT NOT NULL
);
 
CREATE VIEW order_totals AS
SELECT o.order_id, c.name, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;
 
INSERT INTO customers (name) VALUES ('Ada'), ('Grace');
INSERT INTO orders (customer_id, amount) VALUES (1, 120.00), (2, 45.50);
INSERT INTO order_items (order_id, sku, qty) VALUES (1, 'A-100', 2), (2, 'B-200', 1);

Basic DROP TABLE and the "does not exist" error

The simplest form removes one table. If the table is missing, PostgreSQL raises an error and, inside a transaction block, aborts the whole transaction.

CREATE TABLE scratch (id INT);
DROP TABLE scratch;
 
DROP TABLE scratch;

DROP TABLE
ERROR:  table "scratch" does not exist

This matters in migration scripts. If a script drops a table that a previous run already removed, the error stops the script.

DROP TABLE IF EXISTS

Adding IF EXISTS turns the error into a NOTICE and lets the script continue.

DROP TABLE IF EXISTS scratch;

NOTICE:  table "scratch" does not exist, skipping
DROP TABLE

The statement still reports DROP TABLE as its command tag even though nothing was dropped. If you find the notices noisy in psql output, set client_min_messages to warning for the session:

SET client_min_messages = warning;
DROP TABLE IF EXISTS scratch;

IF EXISTS only suppresses the missing-table error. It does not suppress dependency errors, permission errors, or lock timeouts.

Dropping multiple tables in one statement

You can list several tables separated by commas. They are dropped in a single statement, so either all of them go or none does.

CREATE TABLE tmp_a (id INT);
CREATE TABLE tmp_b (id INT);
CREATE TABLE tmp_c (id INT);
 
DROP TABLE IF EXISTS tmp_a, tmp_b, tmp_c, tmp_d;

NOTICE:  table "tmp_d" does not exist, skipping
DROP TABLE

Listing multiple tables also solves a common ordering problem. If order_items references orders through a foreign key, dropping both in one statement works without CASCADE, because PostgreSQL sees that the dependent constraint is being removed by the same command:

-- Works without CASCADE because both sides of the FK are in the list
-- (do not run yet; we need these tables for the next sections)
-- DROP TABLE order_items, orders;

RESTRICT vs CASCADE

By default DROP TABLE behaves as RESTRICT: if anything else depends on the table, the statement fails. CASCADE removes the dependent objects too.

The dependency error

Try to drop orders. Both the view order_totals and the foreign key on order_items depend on it.

DROP TABLE orders;

ERROR:  cannot drop table orders because other objects depend on it
DETAIL:  constraint order_items_order_id_fkey on table order_items depends on table orders
view order_totals depends on table orders
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

The DETAIL lines are the exact list of what CASCADE would remove. Always read them before adding the keyword.

What CASCADE removes and what it keeps

CASCADE drops objects that depend on the table: views and materialized views that select from it, foreign key constraints on other tables that reference it, rules, triggers, and sequences owned by its columns. It does not drop the other tables themselves, and it does not touch their rows.

DROP TABLE orders CASCADE;

NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to constraint order_items_order_id_fkey on table order_items
drop cascades to view order_totals
DROP TABLE

Now confirm what survived:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;
 
SELECT * FROM order_items;
 
SELECT conname
FROM pg_constraint
WHERE conrelid = 'order_items'::regclass;

 table_name
-------------
 customers
 order_items

 item_id | order_id |  sku  | qty
---------+----------+-------+-----
       1 |        1 | A-100 |   2
       2 |        2 | B-200 |   1

     conname
------------------
 order_items_pkey

order_items still exists with all of its rows. Only its foreign key constraint is gone, which means order_id values now point at nothing. That is the real risk of CASCADE: it can quietly leave orphaned data and drop views that applications rely on. The view order_totals is gone as well and must be recreated by hand.

Recreate orders and the view for the remaining sections:

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers (customer_id),
    amount      NUMERIC(10,2) NOT NULL
);
INSERT INTO orders (customer_id, amount) VALUES (1, 120.00), (2, 45.50);
 
ALTER TABLE order_items
    ADD CONSTRAINT order_items_order_id_fkey
    FOREIGN KEY (order_id) REFERENCES orders (order_id);
 
CREATE VIEW order_totals AS
SELECT o.order_id, c.name, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

Finding dependents before you drop

Rather than trying the drop and reading the error, you can query the catalog. Foreign keys that reference a table live in pg_constraint:

SELECT conname,
       conrelid::regclass AS referencing_table
FROM pg_constraint
WHERE contype = 'f'
  AND confrelid = 'orders'::regclass;

          conname           | referencing_table
----------------------------+-------------------
 order_items_order_id_fkey  | order_items

Views and other relations that depend on the table are found through pg_depend and pg_rewrite, because a view's dependency is recorded on its rewrite rule:

SELECT DISTINCT dependent.relname AS dependent_object,
       dependent.relkind
FROM pg_depend d
JOIN pg_rewrite r        ON r.oid = d.objid
JOIN pg_class dependent  ON dependent.oid = r.ev_class
WHERE d.refobjid = 'orders'::regclass
  AND dependent.oid <> 'orders'::regclass;

 dependent_object | relkind
------------------+---------
 order_totals     | v

relkind is v for a view and m for a materialized view. Running these two queries before a drop gives you the same information as the DETAIL block, without aborting a transaction.

DROP TABLE inside a transaction

PostgreSQL DDL is transactional. A DROP TABLE can be rolled back, which is not the case in MySQL, where DDL causes an implicit commit.

BEGIN;
DROP TABLE order_totals_backup;

Assume that fails because the table does not exist; the transaction is now aborted. Start again with a real table:

BEGIN;
DROP TABLE customers CASCADE;
SELECT COUNT(*) FROM customers;

ERROR:  relation "customers" does not exist

Inside the transaction the table is already gone. Roll back and it returns, together with the dropped foreign key and view:

ROLLBACK;
SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM order_totals;

 count
-------
     2

 count
-------
     2

This makes it safe to wrap a migration in BEGIN ... COMMIT, run the drops, verify with a few SELECTs, and only then commit. Disk space for the dropped table's files is released when the transaction commits; until then the files stay on disk so the rollback can succeed.

Locks and lock_timeout

DROP TABLE acquires an ACCESS EXCLUSIVE lock, the strongest lock level. It conflicts with every other lock, including the ACCESS SHARE lock that a plain SELECT holds. If a long-running report is reading the table, the drop waits until that query finishes. While it waits, every new query on the table queues behind the pending drop, so a single slow report can stall the whole application.

Set lock_timeout so the drop gives up instead of blocking others:

SET lock_timeout = '5s';
DROP TABLE IF EXISTS order_totals;

If the lock cannot be obtained in 5 seconds you get:

ERROR:  canceling statement due to lock timeout

Retry later or terminate the blocking session. To see who holds locks on a table:

SELECT l.pid, l.mode, l.granted, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation = 'orders'::regclass;

DROP TABLE vs TRUNCATE vs DELETE

Use DELETE when you need a WHERE clause or triggers. Use TRUNCATE to empty a table you intend to keep. Use DROP TABLE when the table itself should no longer exist. Note the disk space row: DELETE marks rows dead and the space is only reused after VACUUM, whereas DROP and TRUNCATE release the files as soon as the transaction commits.

Ownership: "must be owner of table"

Only the table owner, a member of the owning role, the schema owner, or a superuser can drop a table. Anyone else sees:

CREATE ROLE analyst LOGIN;
SET ROLE analyst;
DROP TABLE orders;

ERROR:  must be owner of table orders

Granting ALL PRIVILEGES does not help; DROP is not a grantable privilege. Either run the statement as the owner, or transfer ownership first:

RESET ROLE;
ALTER TABLE orders OWNER TO analyst;

Then the analyst role can drop it. Remember to RESET ROLE before continuing with the examples.

Dropping a partitioned table

Dropping a partitioned parent removes every partition and their data in one statement, with no CASCADE needed:

CREATE TABLE events (
    event_id   BIGSERIAL,
    created_at DATE NOT NULL,
    payload    TEXT
) PARTITION BY RANGE (created_at);
 
CREATE TABLE events_2026_08 PARTITION OF events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
 
INSERT INTO events (created_at, payload) VALUES ('2026-08-15', 'a'), ('2026-09-02', 'b');

To remove a single partition you can drop it directly, which deletes its rows:

DROP TABLE events_2026_08;

If you want to keep the data, for example to archive it, detach the partition first. It becomes an ordinary standalone table that you can move, dump, or drop later:

ALTER TABLE events DETACH PARTITION events_2026_09;
SELECT COUNT(*) FROM events_2026_09;
DROP TABLE events;

 count
-------
     1
DROP TABLE

events_2026_09 survives the drop of the parent because it is no longer attached. On PostgreSQL 14 and later, DETACH PARTITION ... CONCURRENTLY avoids taking an ACCESS EXCLUSIVE lock on the parent, at the cost of running outside a transaction block.

Dropping all tables in a schema

There is no DROP ALL TABLES statement. You have two options, and both destroy every table in the schema, so verify you are connected to the right database before running either.

Option 1: drop and recreate the schema

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO public;

This also removes functions, types, sequences, and views in the schema, and resets any custom grants. It is the fastest way to reset a development database, but it is too blunt for a shared one.

Option 2: loop over pg_tables

A DO block builds and executes one DROP TABLE ... CASCADE per table. Using format with %I quotes identifiers correctly.

DO $$
DECLARE
    t RECORD;
BEGIN
    FOR t IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = 'public'
    LOOP
        EXECUTE format('DROP TABLE IF EXISTS public.%I CASCADE', t.tablename);
    END LOOP;
END $$;

Because the block runs inside a single transaction, either every table is dropped or none is. Add a RAISE NOTICE 'dropping %', t.tablename; line inside the loop if you want a log of what was removed.

When you are dropping tables from a GUI client such as Chat2DB, right-clicking a table in the tree and choosing drop shows the generated DROP TABLE statement before it runs, so you can add IF EXISTS or check whether CASCADE is really needed. The web version is at https://app.chat2db.ai (opens in a new tab) and the desktop client is at https://chat2db.ai/download (opens in a new tab).

Temporary tables and ON COMMIT DROP

Temporary tables are session scoped and vanish when the connection closes, so you rarely need to drop them explicitly. If a temp table should live only for the current transaction, declare it with ON COMMIT DROP:

BEGIN;
CREATE TEMP TABLE staging ON COMMIT DROP AS
SELECT order_id, amount FROM orders WHERE amount > 100;
SELECT COUNT(*) FROM staging;
COMMIT;
 
SELECT COUNT(*) FROM staging;

 count
-------
     1
COMMIT
ERROR:  relation "staging" does not exist

Note that DROP TABLE IF EXISTS staging without a schema qualifier will drop a temp table named staging before a permanent one of the same name, because pg_temp comes first in the search path. Qualify the name (DROP TABLE public.staging) when both could exist.

Summary

DROP TABLE in PostgreSQL removes a table and its data, and releases disk space as soon as the transaction commits. Use IF EXISTS in scripts so a missing table produces a notice instead of an error. The default RESTRICT behaviour refuses to drop a table with dependents and lists them in DETAIL; CASCADE removes those dependents, which means views, foreign key constraints on other tables, and rules, but never the other tables or their rows. Check pg_constraint and pg_depend first, wrap drops in a transaction so you can roll back, set lock_timeout to avoid stalling the application, and prefer DETACH PARTITION over dropping when you want to keep partition data.

FAQ

Does DROP TABLE CASCADE delete data in other tables?

No. CASCADE drops dependent objects such as views and the foreign key constraints defined on other tables. The referencing tables and all their rows remain. The consequence is that those rows can now hold values that reference a table that no longer exists, so recreate or clean up the constraints afterwards.

Can I roll back a DROP TABLE in PostgreSQL?

Yes, as long as it runs inside a transaction block that has not been committed. PostgreSQL DDL is fully transactional. After ROLLBACK the table, its data, its indexes, and any objects removed by CASCADE are all restored. MySQL does not offer this; its DDL commits implicitly.

Why does DROP TABLE hang?

It is waiting for an ACCESS EXCLUSIVE lock, usually because another session is running a long query or holds an open transaction that touched the table. Query pg_locks joined to pg_stat_activity to find the blocker, and set lock_timeout before the drop so it fails fast instead of queuing every other query behind it.