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

postgresqlpostgresql-9.4

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

Best Answer

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.

Related Question