Howto: drop Postgres tables matching a pattern
Suppose that your database has lots of tables, with similar names (maybe partitions of a bigger table?), here's a quick way to drop them in bulk
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)
AND c.relname ilike 'tbl_2015_%'
This query will search into the pg_catalog for all the tables named tbl_2015_something and it will output a drop command for each one:
Name
-----------------------
drop table public.tbl_2015_01;
drop table public.tbl_2015_02;
drop table public.tbl_2015_03;
......
Save the output to a .sql file and execute it with psql -f yoursqlfile.sql
Et voilà!