Postgres How to drop all tables from a schema with "group" in name?

Question

I have a postgres database schema with dozens of tables i need to drop. I essentially need to drop all the tables in my schema (abcd) with the word "group" in the name. How do i do that without manually going in and writing drop script for each one? Wondering if there is some easy way to do that in postgres sql? or maybe using python?

drop table abcd.group%;

select * from pg_class where relnamespace = 'abcd'::regnamespace and relname ilike 'group%' will get you tables starting with group. If you want group anywhere in name then '%group%'. You could then loop over those in Python or a Postgres function. — Adrian Klaver
– Adrian Klaver, Commented Nov 3, 2020 at 22:37

Daniel Gimenez · Accepted Answer · 2020-11-03 22:37:09Z

1

Well this is probably not the coolest way to do this, but you can run the query below, copy and paste the output and run that.

SELECT 'DROP TABLE ' || schemaname || '.' || relname || ';' "statement"
FROM pg_catalog.pg_statio_user_tables
WHERE schemaname = 'abcd' AND relname ILIKE 'group%';

answered Nov 3, 2020 at 22:37

Daniel Gimenez

21.1k4 gold badges60 silver badges78 bronze badges

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

2 Comments

Linkx_lair Over a year ago

what is "statement"? is that a specific command in postgres?

Daniel Gimenez Over a year ago

I just can leave a column without a name. Otherwise it has no meaning.

Collectives™ on Stack Overflow

Postgres How to drop all tables from a schema with "group" in name?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related