Read more

How to drop all tables in PostgreSQL

Arne Hartherz
January 16, 2019Software engineer at makandra GmbH

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

Option 1: Drop the entire schema

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

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

DROP 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.

DO $$ 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 .

Posted by Arne Hartherz to makandra dev (2019-01-16 11:30)