Remove all tables in a Postgres schema
When working with initial migrations in a project it is often helpful to drop all tables and then re-run all migrations. Instead of removing and re-creating the schema (which also drops every view, type, sequence, function, and permission inside it), you can use this script taken from this SO answer.
1DO $$ DECLARE
2 r RECORD;
3BEGIN
4 FOR r IN (SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'public') LOOP
5 EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', r.schemaname, r.tablename);
6 END LOOP;
7END $$;Note that pg_tables only lists tables — not views, materialized views, sequences, functions, or types. If you need a true reset, drop and re-create the schema instead.