To drop all tables in PostgreSQL safely, first decide whether you need to remove the table objects or only empty them. DROP TABLE removes table definitions and related objects; TRUNCATE removes rows but keeps the tables. “All” must also have a boundary: named tables, one disposable schema, or a broader set of schemas in one database. The examples below target a schema or an explicit table list—not an entire cluster.
Choose the operation that matches what you want removed
| Operation | What it removes | Important behavior |
|---|---|---|
DROP TABLE |
The named table objects, along with their indexes, rules, triggers, and constraints. | Dependencies restrict the drop by default; CASCADE removes dependent objects recursively. |
DROP SCHEMA ... CASCADE |
Every object contained in the named schema, not just tables. | May also remove dependent objects outside that schema. |
TRUNCATE |
Rows in the named tables; table definitions remain. | Can restart owned sequences with RESTART IDENTITY; takes ACCESS EXCLUSIVE locks and does not fire ON DELETE triggers. |
PostgreSQL’s DROP TABLE documentation recommends DELETE or TRUNCATE when the goal is to empty a table without destroying it. Check the documentation for your installed PostgreSQL version before a production change; the cited command reference is for PostgreSQL 18.
As an Amazon Associate I earn from qualifying purchases.
Remove a known set of tables
When you know exactly which tables should go, list them explicitly and start without CASCADE:
Free tools Windows power users keep installed
One-click scans. No signup required.
DROP TABLE app_schema.orders, app_schema.order_items;By default, PostgreSQL uses RESTRICT behavior: if another object depends on a table, the command fails and reports the dependency. Review that detail before deciding whether the dependent object should be removed too. The PostgreSQL dependency-tracking documentation explains that CASCADE removes dependent objects recursively. It is not merely a way to suppress an error.
#1 Best Overall
For example, if another table has a foreign key referencing a dropped table, cascading removes the foreign-key constraint—not the referencing table itself. Read the server’s dependency details, then choose the intended target list or use CASCADE only if all affected dependent objects are meant to go.
Remove everything in one disposable schema
If the schema is dedicated to disposable objects and every object in it should be removed, dropping the schema is more direct than enumerating tables:
Rank #2
DROP SCHEMA app_schema CASCADE;This drops the schema’s contained objects, including objects other than tables. It can also remove dependent objects in other schemas. Confirm the database and schema name, and review dependencies before running it. PostgreSQL documents this behavior in DROP SCHEMA.
Do not substitute a database-wide or cluster-wide operation when the intended boundary is one schema. The command above names one schema; it is not a recommendation to remove all user schemas.
Stage a destructive change in a transaction
PostgreSQL transactions let you inspect the result before deciding whether to commit. For example, in a client session that preserves the transaction:
BEGIN;
DROP SCHEMA app_schema CASCADE;
-- Inspect the result in this session using catalog views or psql commands.
-- If correct:
COMMIT;
-- If incorrect, issue ROLLBACK instead of COMMIT.PostgreSQL documents transaction rollback as canceling changes made in the current transaction. Verify your client’s autocommit and transaction behavior, and follow your operational policy. Once the change is committed, this transaction’s ROLLBACK option is gone; recovery then depends on your backup or archive.
Clear rows while preserving the tables
Use TRUNCATE when the schema, columns, indexes, and constraints must remain but the rows should be removed:
TRUNCATE TABLE app_schema.orders, app_schema.order_items RESTART IDENTITY;RESTART IDENTITY restarts sequences owned by columns of the truncated tables. Truncation takes ACCESS EXCLUSIVE locks, so concurrent operations may be blocked, and it does not fire ON DELETE triggers. Adding CASCADE can include tables that reference the target tables, so inspect the resulting scope first. See PostgreSQL’s TRUNCATE reference for the command’s locking and behavior details.
Check the target and recovery route before committing
- Confirm the active connection is to the intended database, then verify the schema or exact table names.
- Review dependency errors and identify any objects outside the intended scope that a cascade could affect.
- Make sure a tested recovery route exists before committing. PostgreSQL documents SQL dumps, file-system-level backups, and continuous archiving as distinct approaches in its backup and restore documentation.
pg_dump exports one database and creates a consistent export while the database is in use. Custom and directory archive formats support selective restores; a single-database dump is not a cluster-wide backup. A completed dump command alone does not establish that your backup is restorable or meets your recovery-point needs, so confirm restoration procedures for your environment.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
