How to drop all tables in PostgreSQL

To remove all tables from a database (but keep the database itself), you have two options.

Option 1: Drop the entire schema

You will need to re-create the schema and its permissions. This is usually good enough for development machines only.

CopyDROP SCHEMA public CASCADE;
CREATE SCHEMA public;

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

Applications usually use the "public" schema. You may encounter other schema names when working with a (legacy) application's database.

Note that for Rails applications, dropping and recreating the database itself is usually fine in development. You can use bin/rake db:drop db:create for that.

Option 2: Drop each table individually

Prefer this for production or staging servers. Permissions may be managed by your operations team, and you do not want to be the one who messed up permissions on a shared database cluster.

The following SQL code will find all table names and execute a DROP TABLE statement for each.

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

Credits for this loop go to the TablePlus blog Show archive.org snapshot .