PostgreSQL - How to drop all tables?

How to drop all tables in PostgreSQL?

1. Using SQL Query Editor:

You can run these queries to drop the schema and recreate it:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

You also need to run some grants to interact with the database:

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public IS 'standard public schema';

If you don’t want to recreate the schema and restore the default grants:

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

2 From TablePlus GUI:

You can select all the available tables from the right sidebar, right click and choose Delete.., or press Delete key to drop all. Don’t forget to commit changes to the server (Cmd + S) after doing so.

---

Need a good GUI tool for PostgreSQL? Check out TablePlus. It’s native, beautiful, and available for free.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.