How to Drop All Tables in PostgreSQL

Galaxy Glossary

How do I drop all tables in PostgreSQL without reinstalling the database?

Remove every user-defined table from a PostgreSQL database in one operation.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What is the fastest way to drop all tables?

Run DROP SCHEMA public CASCADE; followed by CREATE SCHEMA public;. This wipes every table, view, and sequence in a single transaction, then recreates the default schema.

How do I back up before dropping?

Use pg_dump -Fc yourdb > yourdb.before_drop.dump. The custom format keeps a compressed, restorable snapshot you can reload with pg_restore if anything goes wrong.

When should I prefer individual DROP TABLE statements?

Choose per-table drops when you need to keep extensions, custom types, or data in other schemas. Dynamic SQL or psql meta-commands can generate the list safely.

Can I generate DROP statements automatically?

Yes. In psql run \gexec to execute query output as commands:

SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;'
FROM pg_tables
WHERE schemaname = 'public';
\gexecThis drops only tables, leaving views intact.

How do I reset sequences after recreating tables?

Run ALTER SEQUENCE for each sequence or allow pg_dump --serializable-deferrable to rebuild them when you reload data.

What permissions are required?

You need ownership of the schema or superuser rights. Without them, DROP SCHEMA or DROP TABLE will fail with an “insufficient privilege” error.

Best practice checklist

Back up first
Disconnect client apps
Run inside a transaction on non-prod
Verify with \dt or SELECT from pg_tables

Why How to Drop All Tables in PostgreSQL is important

How to Drop All Tables in PostgreSQL Example Usage


-- Remove all ecommerce tables in one shot
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

How to Drop All Tables in PostgreSQL Syntax


-- Drop everything in the default schema
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

-- Drop one table
DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];

-- Example (ecommerce)
DROP TABLE IF EXISTS "OrderItems" CASCADE; -- also removes dependent views/triggers

Common Mistakes

Running DROP SCHEMA without recreating it. Fix: always run CREATE SCHEMA public; after the CASCADE drop to keep future object creation working.
Dropping tables while applications are connected. Fix: terminate active sessions with pg_terminate_backend() or put the DB in maintenance mode first.

Frequently Asked Questions (FAQs)

Is there a single command to drop all tables?

Yes. DROP SCHEMA public CASCADE; removes every object in the public schema at once.

Will DROP SCHEMA delete extensions?

No. Extensions live in pg_catalog or their own schemas and remain untouched unless explicitly dropped.

How can I undo a full drop?

Restore the pre-drop backup: createdb newdb then pg_restore -d newdb yourdb.before_drop.dump.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie