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?

cetver · Accepted Answer · 2013-07-02 08:45:34Z

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 2, 2013 at 8:45

cetver

11.9k6 gold badges42 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

yee379 Over a year ago

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

cetver Over a year ago

add limit to select or increase database settings (useful utility pgfoundry.org/projects/pgtune)

yee379 Over a year ago

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.

Collectives™ on Stack Overflow

Drop empty tables in postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related