2

How do I drop all tables whose name start with, say, doors_? Can I do some sort of regex using the drop table command?

I prefer not writing a custom script but all solutions are welcomed. Thanks!

hobbes3
  • 28,078
  • 24
  • 87
  • 116

2 Answers2

6

This script will generate the DDL commands to drop them all:

SELECT 'DROP TABLE ' || t.oid::regclass || ';'
FROM   pg_class t
-- JOIN   pg_namespace n ON n.oid = t.relnamespace -- to select by schema
WHERE  t.relkind = 'r'
AND    t.relname ~~ E'doors\_%' -- enter search term for table here
-- AND n.nspname ~~ '%myschema%' -- optionally select by schema(s), too
ORDER  BY 1;

The cast t.oid::regclass makes the syntax work for mixed case identifiers, reserved words or special characters in table names, too. It also prevents SQL injection and prepends the schema name where necessary. More about object identifier types in the manual.

About the schema search path.

You could automate the dropping, too, but it's unwise not to check what you actually delete before you do.

You could append CASCADE to every statement to DROP depending objects (views and referencing foreign keys). But, again, that's unwise unless you know very well what you are doing. Foreign key constraints are no big loss, but this will also drop all dependent views entirely. Without CASCADE you get error messages informing you which objects prevent you from dropping the table. And you can then deal with it.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
5

I normally use one query to generate the DDL commands for me based on some of the metadata tables and then run those commands manually. For example:

SELECT 'DROP TABLE ' || tablename || ';' FROM pg_tables
WHERE tablename LIKE 'prefix%' AND schemaname = 'public';

This will return a bunch of DROP TABLE xxx; queries, which I simply copy&paste to the console. While you could add some code to execute them automatically, I prefer to run them on my own.

Lukáš Lalinský
  • 40,587
  • 6
  • 104
  • 126
  • This is a loaded footgun. If you do not schema-qualify the table names you may end up dropping the wrong table - depending on your `search_path`. (Among other problems with it.) Only use this if you know what you are doing! – Erwin Brandstetter Mar 15 '12 at 07:27
  • Do you ever run `DROP TABLE` commands without knowing what you are doing? – Lukáš Lalinský Mar 15 '12 at 07:30
  • Never. You are absolutely right there. What you may be missing: even if you narrow down the search to one schema, like you have in your answer, the schema may not be in the current `search_path` or another schema may come first and hold a table of the same name. You may be deleting the **wrong** table and never know it, before it is too late. That's why I call this a loaded footgun. – Erwin Brandstetter Mar 15 '12 at 07:34
  • I didn't try to give the perfect code that works in all cases. I was trying to teach hobbes3 that he can use the `pg_` tables to dynamically generate DDL scripts and for this I prefer simplicity. As I said, when running a `DROP TABLE` command, I expect you to know damn well what you are doing. – Lukáš Lalinský Mar 15 '12 at 07:41
  • Thanks for the response and I appreciate the concerns guys. I'm working in a dev environment, and right now I'm just trying out different models, so it's okay to go crazy and `drop table` everywhere. I have the code to recreate them all. – hobbes3 Mar 15 '12 at 07:45
  • 1
    If you teach the art of mass deletion, it's wise to be and make aware of possible collateral damage. And @hobbes, this answer is not just for you, SO is a very public website. – Erwin Brandstetter Mar 15 '12 at 07:49
  • @ErwinBrandstetter You're right. SO is public. Thanks for reminding me. I usually ask very specific questions that probably only apply to my circumstance, but I can see how other people may run into this question. – hobbes3 Mar 15 '12 at 07:54