DROP TABLE in PostgreSQL

DROP TABLE permanently removes a table and all of its data from the PostgreSQL database. By default, PostgreSQL rejects the operation if any other object depends on the table. You can override this with CASCADE, which drops dependent objects too. Because DROP TABLE is fully transactional, wrapping it in a BEGIN / COMMIT block allows rollback if something unexpected is removed.

Syntax

DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];

Drop multiple tables in one statement:

DROP TABLE IF EXISTS table1, table2, table3;

Key options:

• IF EXISTS — issues a NOTICE instead of an error when the table does not exist.
• CASCADE — removes the table and all objects that depend on it (foreign key constraints, views, rules).
• RESTRICT — the default; refuses to drop the table if any dependent objects exist.

Practical Example

Attempting to drop a table that does not exist:

DROP TABLE shipments;
-- ERROR: table "shipments" does not exist

Using IF EXISTS converts the error into a notice:

DROP TABLE IF EXISTS shipments;
-- NOTICE: table "shipments" does not exist, skipping

When a table has dependent foreign key constraints, a plain DROP TABLE fails:

CREATE TABLE products (
  product_id  SERIAL PRIMARY KEY,
  name        VARCHAR(200) NOT NULL,
  price       NUMERIC(10,2) NOT NULL
);

CREATE TABLE order_lines (
  line_id     SERIAL PRIMARY KEY,
  product_id  INT NOT NULL,
  quantity    INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- This fails because order_lines.product_id references products:
DROP TABLE IF EXISTS products;
-- ERROR: cannot drop table products because other objects depend on it
-- HINT: Use DROP ... CASCADE to drop the dependent objects too.

Use CASCADE to drop the table and the foreign key constraint:

DROP TABLE products CASCADE;
-- NOTICE: drop cascades to constraint order_lines_product_id_fkey on table order_lines

Wrap a destructive DROP TABLE in a transaction for safety:

BEGIN;
DROP TABLE IF EXISTS order_lines CASCADE;
DROP TABLE IF EXISTS products CASCADE;
-- Verify what was removed, then:
COMMIT;
-- Or: ROLLBACK; to undo everything

DROP TABLE vs TRUNCATE TABLE

Use TRUNCATE when you want to empty the table but keep its structure. Use DROP TABLE when the table is no longer needed at all.

Testing with Vela

DROP TABLE is one of the most destructive operations in PostgreSQL — there is no undo outside a transaction. Vela database branching lets you test the full cascade on a production-data clone: confirm exactly which tables and constraints will be removed, validate that application code still works after the removal, and only then apply the drop to the live database.

Production Tips

• Always use IF EXISTS in migration scripts so they are idempotent and do not fail on re-runs.
• Use CASCADE only after reviewing dependent objects with \d+ table_name or pg_depend — it can silently remove views and constraints you need.
• Wrap destructive DDL in a BEGIN / COMMIT transaction so you can ROLLBACK if the cascade removes unexpected objects.
• DROP TABLE does not fire row-level ON DELETE triggers — if trigger logic must execute, DELETE the rows first.
• You must be the table owner, schema owner, or a superuser to drop a table.
• Consider TRUNCATE if you only need to empty the table and want to preserve its structure for future use.