Posted about 1 month ago. Visible to the public. Linked content.

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.

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

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

Once an application no longer requires constant development, it needs periodic maintenance for stable and secure operation. makandra offers monthly maintenance contracts that let you focus on your business while we make sure the lights stay on.

Owner of this card:

Avatar
Arne Hartherz
Last edit:
about 1 month ago
by Arne Hartherz
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Arne Hartherz to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more