How to Drop All Tables in PostgreSQL Safely (2026)

Why you are here

You ran a migration that went sideways, or you are resetting a local dev
database, or a test suite needs a clean slate. The psql console has 40 tables
and you do not want to type DROP TABLE 40 times.

There is a one-command way to do it. There is also a PostgreSQL 15 change that
silently breaks it the first time you try, and a Supabase-specific caveat that
makes the naive command dangerous on a hosted project. This article is the
reset path I use, with the gotchas inline.

The original Stack Overflow thread ("How can I drop all the tables in a
PostgreSQL database?") has been viewed more than 1.5 million times for a
reason: every developer hits this during a migration at least once.

The one-command reset

-- ⚠️  Destructive. Drops every table, view, sequence, and function in public.
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

DROP SCHEMA public CASCADE removes the public schema and every object that
depends on it — tables, views, materialized views, sequences, functions, and
triggers. CASCADE is what saves you from listing the dependencies in order.
Then you recreate the empty schema and restore the default grants.

That second GRANT ... TO public line is where the PostgreSQL 15 gotcha lives.

The PostgreSQL 15 gotcha that breaks this

PostgreSQL 15 changed two defaults on the public schema, and both of them
bite you when you drop and recreate it:

1. PUBLIC no longer has CREATE on public by default. Before 15, every role could create objects in public. From 15 onward, on new databases and new clusters, that privilege is revoked. This is the secure-schema pattern PostgreSQL has recommended since CVE-2018-1058, now made the default.
2. The owner of public is now pg_database_owner, not the bootstrap superuser. This lets database owners manage public without being a superuser.

When you run CREATE SCHEMA public on a 15+ database, the schema comes back
with these new secure defaults. Your application role — the one that runs
INSERT, CREATE TABLE, migrations — no longer has CREATE on public. The
next migration or insert throws:

ERROR: permission denied for schema public

If you already hit that, the fix is to grant explicitly to the role your app
actually uses, instead of relying on PUBLIC:

CREATE SCHEMA public;
GRANT USAGE ON SCHEMA public TO app_role;
GRANT CREATE  ON SCHEMA public TO app_role;

On Supabase, the anon and authenticated roles also need USAGE on public or
your client queries fail with the same error. For a deeper write-up of that
specific failure, see Supabase client "permission denied for schema public"
fix.

The variant that keeps extensions

DROP SCHEMA public CASCADE also drops every extension installed in the
public schema. uuid-ossp, pgcrypto, pg_stat_statements (if installed
there), and — critically on Supabase — pgvector all live somewhere. If you
rely on gen_random_uuid(), uuid_generate_v4(), or vector columns, they
vanish and you get:

ERROR: function gen_random_uuid() does not exist

Reinstall them after recreating the schema:

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

-- Reinstall the extensions you actually use.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS vector;   -- pgvector

If you never want to lose extensions during a reset, drop the tables only and
leave the schema (and its extensions) intact — see the selective script below.

Drop only tables, keep everything else

When extensions and functions must survive the reset, drop tables individually
via information_schema instead of nuking the whole schema:

DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT tablename FROM pg_tables
    WHERE schemaname = 'public'
  LOOP
    EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
  END LOOP;
END $$;

This loops over every table in public and drops it with CASCADE (so views
and foreign keys depending on it go too). Functions, sequences created by
SERIAL columns (they get dropped with their tables), and extensions stay.

To also reset sequences after reloading data:

SELECT setval(pg_get_serial_sequence('public.' || quote_ident(t.tablename), 'id'),
              1, false)
FROM pg_tables t
WHERE schemaname = 'public';

The Supabase path: do not run DROP SCHEMA on production

On a hosted Supabase project, public is not the only schema that matters. The
auth, storage, realtime, and graphql schemas are all managed by Supabase
and reference objects that your tables depend on. Running
DROP SCHEMA public CASCADE on a production project can cascade into Supabase's
internal objects and break the dashboard, auth, and storage.

For a dev project, use the controlled reset:

# CLI: drops and rebuilds everything from your migrations folder
supabase db reset

Or in the dashboard: Project Settings → Database → Reset database. Both
rebuild the schema from your migration files, which is the safe path — your
schema is reproducible from code, not from memory.

For production, never reset. Restore from a backup or a pg_dump snapshot:

pg_dump "$DATABASE_URL" -F c -f backup.dump
# ... later, to restore:
pg_restore -d "$DATABASE_URL" -c backup.dump

If the reset is part of a failed migration, follow the
PostgreSQL migration rollback playbook
instead of dropping tables by hand.

The transaction wrapper for dev

On a local database, wrap the reset in a transaction so a typo does not leave
you in a half-dropped state:

BEGIN;
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
-- sanity check: SELECT 1;
COMMIT;
-- if something looked wrong: ROLLBACK; (but the schema is already gone)

Note: DROP/CREATE SCHEMA inside a transaction is safe to ROLLBACK —
PostgreSQL transactional DDL means a rollback restores the dropped schema. This
is one of the features that makes Postgres migrations far less scary than other
engines.

Production safety checklist

Before you run any of this against anything that is not a throwaway dev
database:

1. Take a backup first. pg_dump -F c is cheap insurance.
2. Confirm the connection string. \conninfo in psql — verify you are not pointed at prod. The number of times a reset has hit the wrong database is exactly why this section exists.
3. Block new connections during the reset if other services share the DB: ALTER DATABASE yourdb CONNECTION LIMIT 0; then terminate backends, reset, then restore the limit.
4. Have a tested restore path. A backup you have never restored from is a wish, not a backup.

For the broader posture — when to roll forward vs roll back, how to stage
migrations so a reset is never the plan — read the
database design and optimization guide.

Common mistakes

• Forgetting CASCADE. Without it, DROP SCHEMA refuses to run if any object exists in it. CASCADE is the whole point.
• Running it in the wrong database. Use SELECT current_database(); to confirm before you press enter. \l lists databases.
• Not re-granting on PostgreSQL 15+. The schema recreates with secure defaults and your app role loses CREATE. See the gotcha section above.
• Dropping on a hosted Supabase prod. Use supabase db reset on dev only.
• Dropping extensions by accident. Reinstall uuid-ossp, pgcrypto, pgvector after CREATE SCHEMA, or use the table-only loop.

When you should not do this at all

A full reset is a dev convenience. In production, "drop everything and start
over" is almost always the wrong instinct. The right move is usually a targeted
migration: ALTER TABLE, a backfill, a column drop behind a feature flag. If
you are resetting because a migration is too tangled to untangle, the fix is
to write smaller, reversible migrations going forward — not to make resetting
easier.

The RLS debugging guide covers the same
instinct for policy drift: most "blow it away" urges are really "I cannot see
what changed" problems.

TL;DR

-- Dev reset, PostgreSQL 15+:
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT USAGE, CREATE ON SCHEMA public TO app_role;  -- re-grant your app role
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";        -- restore extensions
CREATE EXTENSION IF NOT EXISTS pgcrypto;

On Supabase dev: supabase db reset. On Supabase prod: restore from a backup,
do not drop the schema.

Related Articles

• Show Tables in PostgreSQL (psql)
• PostgreSQL DESCRIBE TABLE Equivalent
• Change a PostgreSQL User Password
• Supabase "permission denied for schema public" Fix
• PostgreSQL Migration Rollback Playbook
• Next.js + Supabase Database Design and Optimization
• Debug Supabase RLS Issues

---

Originally published at https://www.iloveblogs.blog