How to drop multiple tables in PostgreSQL using a wildcard

VERIFY before you apply

Prior to running the DROP TABLE command, it's a good idea to double-check which tables you'll be affecting. A little prudence can avoid a whopping headache later on. Consider logging the tables you'll be affecting using the RAISE INFO before executing the drop statement, or better yet, pull them into a list:

SELECT tablename 
FROM pg_tables 
WHERE tablename LIKE 'pattern%';


Take a moment to review this list to ensure you won't be dropping any table you'll later miss.

Bulk table dropping: advanced strategies

While wildcard patterns delivered through the LIKE clause typically get the job done, there are times where more advanced techniques are necessary. For instance, you may want to use ILIKE for a case-insensitive pattern match or even go for pg_catalog tables for advanced table filtering.

The art of crafting dynamic SQL generation

Rather than immediately executing dynamic SQL statements, you might want to first generate these statements. Once created, you can pore over them at your leisure before deciding to pull the trigger. To create a SQL script for dropping tables, use:

SELECT 'DROP TABLE IF EXISTS ' || quote_ident(tablename) || ' CASCADE;' 
FROM pg_tables 
WHERE tablename LIKE 'pattern%';


Run the resulting script with psql -f drop_tables.sql after you've given it a thorough once-over.

Filtering, the backbone of table scripting

When you're working within the confines of a script, adding WHERE clause for filtering can enhance the functionality of your commands:

SELECT 'DROP TABLE IF EXISTS ' || quote_ident(schemaname) || '.' || quote_ident(tablename) || ' CASCADE;' 
FROM pg_tables 
WHERE schemaname = 'public'
  AND tablename LIKE 'pattern%';


This script narrows down the focus to only those tables housed within the public schema.

Terminal and text editors in the mix

If you're a fan of the Unix-based terminal, psql or vim, you can leverage these tools to tweak the process. Retrieve a newline-separated list of tables with \dt and a wildcard, then convert this to a comma-separated list using vim or another text editor. With the final list, you can execute a DROP TABLE statement.

Be safe, not sorry

In PostgreSQL, dropping tables is BIG stuff—especially in a production environment. Cinch on your safety belts:

• Backup the databases prior to dropping the tables.
• Test the drop script on a non-production instance.
• Use BEGIN and ROLLBACK to first test the script before hitting the big red button (committing changes).
• Keep a close eye on logs and RAISE INFO outputs to monitor activities.

Dealing with the aftermath

Post table drop: cleanup time. Vacuuming, updating stats, and disk space reclamation might be on the cards. Collaborate with your DBA team to adhere to the best practices.