DbSchema | How to Drop All Tables from PostgreSQL

Publish on DbSchema Blog >>>

1.Drop all tables by deleting the schema

This example will drop all tables from the public schema.

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

If you want to drop tables from another schema, replace public with the name of your schema.

The first statement will drop the current schema and the next one will create a new schema with the same name but without any table.

After executing the two queries, you may also want to restore the grants for the new schema. Do this by executing:

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

2.Drop all tables without deleting the schema

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 $$;

3.Drop all tables from DbSchema

Using DbSchema you can drop all the tables from a PostgreSQL database simply by selecting all the tables from the left menu, right-clicking on them and select the ‘Drop’ option.

DbSchema is a visual diagram designer that allows you to edit & browse databases from an interactive diagram. Use powerful query tools to manage complex databases, export HTML5 documentation or work with the schema offline.

Download DbSchema for Free