5

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'

GoldenRetriever
  • 155
  • 3
  • 11
  • here's a link to a question that might have the answers you need:[How can I drop all the tables in a PostgreSQL database?](https://stackoverflow.com/questions/3327312/how-can-i-drop-all-the-tables-in-a-postgresql-database) – eLMagnifico Jan 13 '20 at 16:33

2 Answers2

10

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;
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • I am sure how to call such statements with psycopg2, unfortunately. – GoldenRetriever Jan 14 '20 at 14:31
  • what happens if you pass this code inside the `writeCon.execute()` function?e.g. `writeCon.execute("DO $$ DECLARE row record; BEGIN FOR row IN SELECT * FROM pg_tables WHERE schemaname = 'mySchema' LOOP EXECUTE 'DROP TABLE mySchema.' || quote_ident(row.tablename); END LOOP; END; $$;")` – Jim Jones Jan 14 '20 at 15:36
  • it says that I need to add a Cascade because there are some dependencies. Where do I add that? – GoldenRetriever Jan 14 '20 at 15:46
  • @GoldenRetriever try adding a `CASCADE` to the drop table statement: `DROP TABLE mySchema.' || quote_ident(row.tablename) CASCADE;` – Jim Jones Jan 14 '20 at 15:47
  • Error: psycopg2.errors.SyntaxError: syntax error at or near "CASCADE" LINE 3: 'mySchema.' || quote_ident(row.tablename) CASCADE; E... – GoldenRetriever Jan 14 '20 at 15:51
  • something like this: `writeCon.execute("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; $$;")` – Jim Jones Jan 14 '20 at 15:56
  • It works. Thanks :) What is this syntax ? I am new to SQL but have never seen this kind of syntax. – GoldenRetriever Jan 14 '20 at 16:17
3

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.

JGH
  • 15,928
  • 4
  • 31
  • 48