snippetsqlMajor

postgres how to drop all the table in a schema through command

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thepostgresallthroughschemadrophowcommandtable

Problem

I use postgresql and I need to drop all the tables which are present in the schema. How can I delete from command prompt.

Solution

You want to use the CASCADE option of DROP SCHEMA. From the documentation:

CASCADE - Automatically drop objects (tables, functions, etc.) that are
contained in the schema, and in turn all objects that depend on those
objects

BE CAREFUL - emphasis above mine.

Obviously you'll need to recreate the schema afterwards.

To just drop all tables in the current schema, you can use this script:

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

Change WHERE schemaname = current_schema() if you want to drop all of the tables in a different schema.

Code Snippets

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

Context

StackExchange Database Administrators Q#152462, answer score: 26

Revisions (0)

No revisions yet.