How to delete every table in a specific schema in postgres?

Question

How do I delete all the tables I have in a specific schema? Only the tables in the schema should be deleted. I already have all the table names that I fetched with the code below, but how do delete all those tables?

The following is some psycopg2 code, and below that is the SQL generated

writeCon.execute("SELECT table_name FROM information_schema.tables WHERE table_schema='mySchema'")

SELECT table_name FROM information_schema.tables WHERE table_schema='mySchema'

Answer 1

You can use an anonymous code block for that.

WARNING: This code is playing with DROP TABLE statements, and they are really mean if you make a mistake ;) The CASCADE option drops all depending objects as well. Use it with care!

DO $$
DECLARE
  row record;
BEGIN
    FOR row IN SELECT * FROM pg_tables WHERE schemaname = 'mySchema' 
    LOOP
      EXECUTE 'DROP TABLE mySchema.' || quote_ident(row.tablename) || ' CASCADE';
    END LOOP;
END;
$$;

In case you want to drop everything in your schema, including wrappers, sequences, etc., consider dropping the schema itself and creating it again:

DROP SCHEMA mySchema CASCADE;
CREATE SCHEMA mySchema;

Answer 2

For a single-line command, you can use psql and its \gexec functionality:

SELECT format('DROP TABLE %I.%I', table_schema, table_name)
FROM information_schema.tables
WHERE table_schema= 'mySchema';\gexec

That will run the query and execute each result string as SQL command.