Search code examples
postgresqlsql-drop

Drop empty tables in postgres

In my haste partition a rather large table, I created a simple sql script to create about 4,000 tables. now that things have calmed down, I can see that I have no use for most of the partitions as I have had zero inserts into them.

I now want to clean up those empty tables - how can I drop them using SQL? I believe I have part of the solution:

SELECT relname,n_live_tup
  FROM pg_stat_user_tables
  WHERE n_live_tup=0;

How do I chain that up with a DROP?

Solution

  • Try this

    CREATE OR REPLACE FUNCTION drop_table(name TEXT)
  RETURNS void AS
$BODY$
DECLARE statement TEXT;
BEGIN
statement := 'DROP TABLE ' || name;
EXECUTE statement;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

SELECT drop_table(relname)
FROM pg_stat_user_tables
WHERE n_live_tup = 0;