Drop Table

The DROP TABLE statement permanently removes a table and all of its data from the database. By default PostgreSQL rejects the operation if any other object — such as a view or a foreign key constraint — depends on the table. You can override this with CASCADE, which drops the dependent objects too.

Syntax and options

The basic syntax is:

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

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, etc.).
• RESTRICT — the default; refuses to drop the table if any dependent objects exist.

To drop multiple tables in one statement, separate their names with commas:

DROP TABLE IF EXISTS tvshows, animes;

Practical examples

Attempting to drop a table that does not exist raises an error:

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

Using IF EXISTS converts the error into a notice:

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

When a table has dependent objects, DROP TABLE fails without CASCADE:

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  firstname VARCHAR(50) NOT NULL,
  lastname  VARCHAR(50) NOT NULL
);

CREATE TABLE pages (
  page_id   SERIAL PRIMARY KEY,
  title     VARCHAR(255) NOT NULL,
  author_id INT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

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

Add CASCADE to drop both the table and the foreign key constraint on pages:

DROP TABLE authors CASCADE;
-- NOTICE:  drop cascades to constraint pages_author_id_fkey on table pages

Production tips

• Always use IF EXISTS in migration scripts so they are idempotent and do not fail on re-runs.
• Use CASCADE with caution — review which dependent objects will be removed before running it in production.
• You need to be a superuser, schema owner, or table owner to drop a table.
• Wrap destructive DDL in a transaction so you can roll back if something unexpected is dropped.
• Consider TRUNCATE instead of DROP TABLE when you want to keep the table structure but remove all rows quickly.