How to drop multiple tables in PostgreSQL using a wildcard

Question

When working with partitions, there is often a need to delete all partitions at once.

However

DROP TABLE tablename* 

Does not work. (The wildcard is not respected).

Is there an elegant (read: easy to remember) way to drop multiple tables in one command with a wildcard?

Answer 1

Use a comma separated list:

DROP TABLE foo, bar, baz; 

If you realy need a footgun, this one will do it's job:

CREATE OR REPLACE FUNCTION footgun(IN _schema TEXT, IN _parttionbase TEXT)  RETURNS void  LANGUAGE plpgsql AS $$ DECLARE     row     record; BEGIN     FOR row IN          SELECT             table_schema,             table_name         FROM             information_schema.tables         WHERE             table_type = 'BASE TABLE'         AND             table_schema = _schema         AND             table_name ILIKE (_parttionbase || '%')     LOOP         EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || ' CASCADE ';         RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);     END LOOP; END; $$;  SELECT footgun('public', 'tablename'); 

Answer 2

Here's another hackish answer to this problem. It works in ubuntu and maybe some other os too. do a \dt in postgres command prompt(the command prompt was running inside genome-terminal in my case). Then you'll see a lot of tables in the terminal. Now use ctrl+click-drag functionality of the genome-terminal to copy all tables' names. Open python, do some string processing(replace ' ' by '' and then '\n' by ',') and you get comma separated list of all tables. Now in psql shell do a drop table CTRL+SHIFT+V and you're done. I know it's too specific I just wanted to share. :)