Howto: drop Postgres tables matching a pattern

Suppose that your Postgres db has a big, big table split into many smaller partitions, how can you delete some of them in single query?

The Solution

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à!