Drop All Tables in PostgreSQL: Safe Methods, CASCADE, and TRUNCATE

The test database has to be empty before the next run, and PostgreSQL has no DROP ALL TABLES statement. The shortest route drops the schema that holds the tables and builds a new one:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Those two statements take the views, sequences, types and functions with them, and hand the new schema a fresh set of privileges. Three narrower routes leave those in place: generate one DROP TABLE per table, loop over pg_tables in a DO block, or keep the tables and empty them with TRUNCATE.

Everything below runs on PostgreSQL 18, in the public schema of a database that holds two tables and one view:

CREATE TABLE customers (customer_id int PRIMARY KEY, name text NOT NULL);

CREATE TABLE orders (
    order_id    int PRIMARY KEY,
    customer_id int NOT NULL REFERENCES customers,
    total       numeric(10,2) NOT NULL
);

CREATE VIEW customer_totals AS
SELECT c.customer_id, sum(o.total) AS total
FROM customers c
JOIN orders o USING (customer_id)
GROUP BY c.customer_id;

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Grace');
INSERT INTO orders VALUES (10, 1, 40.00), (11, 1, 12.50), (12, 2, 99.90);

Before you start

Four checks cost a minute and save the afternoon:

1. Confirm where you are connected. In psql, \conninfo prints the database, the user, the host and the port.
2. Decide whether you want the tables gone or the whole schema, views, sequences and functions included.
3. Take a backup if there is any chance the data is wanted later.
4. Look at which objects depend on the tables, because CASCADE removes those too.

PostgreSQL runs DDL inside transactions, and that is the safety net under every method below. BEGIN states it without exceptions for DDL: "all statements after a BEGIN command will be executed in a single transaction until an explicit COMMIT or ROLLBACK is given". TRUNCATE is covered by the same rule, since it "is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit". One thing did change between releases, and the next section covers it: the privileges and the owner of a recreated public schema. Do not run any of this against a production database, and do not run it anywhere without a backup you have restored at least once.

Drop the public schema with CASCADE and recreate it

Recreating the schema is the whole method:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Everything the schema held goes with it, materialized views and extension objects included. That is the method for a development database you want back in its original state, and the wrong method when objects you did not create live in public too.

The recreated schema is not the one you dropped. public starts out owned by pg_database_owner, and CREATE SCHEMA without an AUTHORIZATION clause gives the new one to "the user executing the command". The old schema's grants went with it, so an application that connects as another role loses its access until you restore them:

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

The second statement deserves a thought before you paste it. It restores the behavior of PostgreSQL 14 and earlier, where every user could create objects in public; on PostgreSQL 15 and later, the schema documentation reserves that privilege for "databases upgraded from PostgreSQL 14 or earlier". Grant it only when your application depends on it.

Generate the DROP TABLE statements and review them

When only the tables are meant to go, and you want to see the list before anything runs, let PostgreSQL write the script:

SELECT string_agg(
         format('DROP TABLE IF EXISTS %I.%I CASCADE;', schemaname, tablename),
         E'\n'
         ORDER BY tablename
       ) AS drop_sql
FROM pg_tables
WHERE schemaname = 'public';

The single column comes back with one statement per line:

DROP TABLE IF EXISTS public.customers CASCADE;
DROP TABLE IF EXISTS public.orders CASCADE;

format with %I quotes each identifier the way PostgreSQL needs it, which is what keeps a table called order or Mixed Case from breaking the script. Paste the result back and run it inside a transaction to rehearse the whole thing:

BEGIN;

DROP TABLE IF EXISTS public.customers CASCADE;
DROP TABLE IF EXISTS public.orders CASCADE;

ROLLBACK;

The ROLLBACK puts every table back, and any error the script would have raised has already shown up in your session by then. Change the last line to COMMIT when the list is the list you meant.

Drop only the tables with a PL/pgSQL loop

For a script that runs unattended, the same work fits in an anonymous block:

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

The loop reads pg_tables, so it names only tables. It does not follow that only tables disappear:

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_type, table_name;

(0 rows)

The view went with the tables it was built on, because DROP TABLE with CASCADE "will remove a dependent view entirely". Objects with no dependency on the tables, such as a function or an enum type, are still there. Leave CASCADE off and PostgreSQL protects the view instead, since RESTRICT is the default and it will "refuse to drop the table if any objects depend on it", which turns the script into a list of errors that tells you exactly what is attached to what.

Empty every table with TRUNCATE and keep the structure

When the tables, indexes, constraints and grants are meant to survive and only the rows are in the way, TRUNCATE is both faster and less disruptive than dropping and recreating:

DO $$
DECLARE
  tables_to_truncate TEXT;
BEGIN
  SELECT string_agg(format('%I.%I', schemaname, tablename), ', ' ORDER BY tablename)
  INTO tables_to_truncate
  FROM pg_tables
  WHERE schemaname = current_schema();

  IF tables_to_truncate IS NOT NULL THEN
    EXECUTE 'TRUNCATE TABLE ' || tables_to_truncate || ' RESTART IDENTITY CASCADE';
  END IF;
END $$;

SELECT count(*) AS orders_left FROM orders;

Three parts of that statement matter:

• TRUNCATE "has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster", and it "reclaims disk space immediately".
• RESTART IDENTITY restarts the sequences owned by the truncated columns to their start value, so a fixture loads the same ids on every run.
• CASCADE truncates "all tables that have foreign-key references to any of the named tables".

CASCADE is insurance here rather than a requirement. The rule is that "TRUNCATE cannot be used on a table that has foreign-key references from other tables, unless all such tables are also truncated in the same command", and the block names every table in the schema, so customers and orders go in one command either way. What CASCADE adds is the referencing table the list never named, one in another schema for instance, whose rows it truncates as well.

One caveat belongs with it: the statement takes an ACCESS EXCLUSIVE lock on each table, "which blocks all other concurrent operations on the table". On a shared development database, run it when nobody else is reading.

Drop all tables in a specific schema

Every block above uses current_schema(), which follows the search_path of the session that runs it. A script run by a job, by another user, or through a connection pool can therefore land somewhere you did not intend. Name the schema instead:

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

Before a schema-wide cleanup it is worth listing what is in there: Show Tables in PostgreSQL covers the catalog queries, and List All Schemas in PostgreSQL the schemas themselves.

Drop tables visually in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

The risk in all of this is not the syntax. It is running the script against the wrong database, or discovering afterwards which reports were reading the tables you removed. DbSchema puts both in front of you before the statement runs:

1. Connect through the PostgreSQL JDBC driver and let DbSchema reverse-engineer the schema onto an interactive diagram, where every foreign key is a line, so the tables a CASCADE will reach are visible rather than inferred.
2. Set Highlight on the connection Settings tab to Production, Development or Test, and DbSchema colors that connection everywhere in the application. Tick Read Only Connection on the same tab for a database nobody should be dropping from, and DbSchema refuses every schema and data modification made through it.
3. Paste the generated script into the SQL Editor and run it with Run Script. DbSchema keeps Commit and Rollback in the toolbar, so a script you ran and do not like goes back the way it came.
4. Click Refresh Model from Database afterwards, and the diagram matches the database again.

Reverse-engineering and the refresh read the database into the local .dbs model file and change nothing in PostgreSQL. The statements you run in the SQL Editor are the ones that drop tables, and they go to the database as written.

The next reset goes better with the dependencies in front of you. Get DbSchema from https://dbschema.com/download.html, connect to the database you are about to clear, and check on the diagram which tables the foreign keys tie together before you choose between DROP SCHEMA and a list of DROP TABLE statements. Connecting, reverse-engineering, the diagram and the SQL Editor are in the free Community Edition, and saving that model to a file and comparing it against another database are in Pro.

FAQ

Can I roll back dropping tables in PostgreSQL?

Yes, as long as the statements ran inside an explicit transaction you have not committed. DROP DATABASE is the exception, since it "cannot be executed inside a transaction block", so that one is final the moment it succeeds.