Drop empty tables in postgres

Question

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?

score 1 · Accepted Answer · answered Jul 02 '13 at 08:45

1

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;

answered Jul 02 '13 at 08:45

cetver

11,279
5
36
56

darn, i was hoping not to write a function... anyway, no luck - i get: ptolemy_production=> SELECT drop_table(relname) ptolemy_production-> FROM pg_stat_user_tables ptolemy_production-> WHERE n_live_tup = 0; WARNING: out of shared memory CONTEXT: SQL statement "DROP TABLE spanning_tree__neighbour__vlan3801" PL/pgSQL function drop_table(text) line 5 at EXECUTE statement ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. CONTEXT: SQL statement "DROP TABLE spanning_tree__neighbour__vlan3801" PL/pgSQL function drop_table(text) line 5 at EXECUTE statement – yee379 Jul 02 '13 at 20:38
add `limit` to `select` or increase database settings (useful utility http://pgfoundry.org/projects/pgtune/) – cetver Jul 03 '13 at 06:17
i'm accepting this answer, even tho in the end i just did a copy and paste of empty tables and parsed it with the `-f` flag on psql. – yee379 Jul 06 '13 at 05:02

Drop empty tables in postgres

1 Answers1