How to drop multiple tables in PostgreSQL using a wildcard

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?

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');

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. :)

I used this.

echo "select 'drop table '||tablename||';' from pg_tables where tablename like 'name%'" | \
    psql -U postgres -d dbname -t | \
    psql -U postgres -d dbname

Substitute in appropriate values for dbname and name%.

I've always felt way more comfortable creating a sql script I can review and test before I run it than relying on getting the plpgsql just right so it doesn't blow away my database. Something simple in bash that selects the tablenames from the catalog, then creates the drop statements for me. So for 8.4.x you'd get this basic query:

SELECT 'drop table '||n.nspname ||'.'|| c.relname||';' as "Name" 
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
     AND n.nspname <> 'pg_catalog'
     AND n.nspname <> 'information_schema'
     AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid);

Which you can add a where clause to. (where c.relname ilike 'bubba%')

Output looks like this:

         Name          
-----------------------
 drop table public.a1;
 drop table public.a2;

So, save that to a .sql file and run it with psql -f filename.sql

Disclosure: this answer is meant for Linux users.

I would add some more specific instructions to what @prongs said:

• \dt can support wildcards: so you can run \dt myPrefix* for example, to select only the tables you want to drop;
• after CTRL-SHIFT-DRAG to select then CTRL-SHIFT-C to copy the text;
• in vim, go to INSERT MODE and paste the tables with CTRL-SHIFT-V;
• press ESC, then run :%s/[ ]*\n/, /g to translate it to comma-separated list, then you can paste it (excluding the last comma) in DROP TABLE % CASCADE.

Using linux command line tools, it can be done this way:

psql -d mydb -P tuples_only=1 -c '\dt' | cut -d '|' -f 2 | paste -sd "," | sed 's/ //g' | xargs -I{} echo psql -d mydb -c "drop table {};"

NOTE: The last echo is there because I couldn't find a way to put quotes around the drop command, so you need to copy and paste the output and add the quotes yourself.

If anyone can fix that minor issue, that'd be awesome sauce.

So I faced this problem today. I loaded my server db through pgadmin3 and did it that way. Tables are sorted alphabetically so shift and click followed by delete works well.

I like the answer from @Frank Heikens. Thanks for that. Anyway I would like to improve a bit;

Let's assume our partitioned table name is partitioned_table and we have a number suffix which we increase each time. Like partitioned_table_00, partitioned_table_01 ... partitioned_table_99

CREATE OR REPLACE drop_old_partitioned_tables(schema_name TEXT, partitioned_table_name TEXT, suffix TEXT)
    RETURNS TEXT
    LANGUAGE plpgsql
AS
$$
DECLARE
    drop_query text;
BEGIN
    SELECT 'DROP TABLE IF EXISTS ' || string_agg(format('%I.%I', table_schema, table_name), ', ')
    INTO drop_query
    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE'
      AND table_schema = schema_name
      AND table_name <= CONCAT(partitioned_table_name, '_', suffix) -- It will also drop the table which equals the given suffix
      AND table_name ~ CONCAT(partitioned_table_name, '_\d{2}');
    IF drop_query IS NULL THEN
        RETURN 'There is no table to drop!';
    ELSE
        EXECUTE drop_query;
        RETURN CONCAT('Executed query: ', (drop_query));
    END IF;
END;
$$;

and for the execution, you can run the below code;

SELECT drop_old_partitioned_tables('public', 'partitioned_table', '10')

Just a side note, if you want to partition your table for each year, your table suffix should be year like partitioned_table_2021. Even if your data bigger which cannot be partitionable for annually, you can do that monthly like partitioned_table_2021_01. Don't forget to adjust your code depending on your needs.

Another solution thanks to Jon answer:

tables=`psql -d DBNAME -P tuples_only=1 -c '\dt' |awk -F" " '/table_pattern/ {print $3","}'`
psql -d DBNAME -c "DROP TABLE ${tables%?};";