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'

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? — eLMagnifico
– eLMagnifico, Commented Jan 13, 2020 at 16:33

Jim Jones · Accepted Answer · 2022-04-07 18:00:32Z

15

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;

edited Apr 7, 2022 at 18:00

answered Jan 13, 2020 at 16:50

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

GoldenRetriever Over a year ago

I am sure how to call such statements with psycopg2, unfortunately.

Jim Jones Over a year ago

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

GoldenRetriever Over a year ago

it says that I need to add a Cascade because there are some dependencies. Where do I add that?

Jim Jones Over a year ago

@GoldenRetriever try adding a CASCADE to the drop table statement: DROP TABLE mySchema.' || quote_ident(row.tablename) CASCADE;

GoldenRetriever Over a year ago

Error: psycopg2.errors.SyntaxError: syntax error at or near "CASCADE" LINE 3: 'mySchema.' || quote_ident(row.tablename) CASCADE; E...

|

JGH · Accepted Answer · 2020-01-13 18:25:21Z

5

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.

answered Jan 13, 2020 at 18:25

JGH

18.2k5 gold badges40 silver badges57 bronze badges

5 Comments

GoldenRetriever Over a year ago

What is \gexec ?

JGH Over a year ago

@GoldenRetriever see the doc

Raul Chiarella Over a year ago

Invalid command \gexec. Try \? for help.

JGH Over a year ago

@RaulChiarella it seems you have added an extra . after the command.

Raul Chiarella Over a year ago

No dot. Just copied pasted exactly as it is, it is postgres that adds the dot.

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related