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: - PUBLIC no longer hasCREATE onpublic by default. Before 15, every role could create objects inpublic . 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. - The owner of public is nowpg_database_owner , not the bootstrap superuser. This lets database owners managepublic 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: - Take a backup first. pg_dump -F c is cheap insurance. - Confirm the connection string. \conninfo inpsql - verify you are not pointed at prod. The number of times a reset has hit the wrong database is exactly why this section exists. - 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. - 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 afterCREATE 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 Top comments (0)