Postgres - How to drop all tables in a database except for 3

Question

I know that to drop a table in a database is as follows, but if I have a database that has over a dozen tables and I only need to keep 3 of them, am I able to delete all but the 3 with only one command?

DROP TABLE IF EXISTS c_note RESTRICT;

Answer 1

Yes, but you need to enumerate over all the tables you want to drop. There's no command to drop all but 3. So, if you had the following tables:

1. foo
2. bar
3. baz
4. narf
5. poit
6. troz

And you wanted to drop the first three and keep the last three, you would use the command:

 DROP TABLE foo, bar, baz;

If you know the tables all exist, there's no need for IF EXISTS, although it won't hurt. RESTRICT is also not needed -- that's the default (the opposite is CASCADE, where you also drop dependant objects).

SQL Doc

Answer 2

The following query will simmply delete all the tables but keep your schema

select 'drop table if exists ' || tablename || ' cascade;' 
    from pg_tables 
    where schemaname = 'public' 

Write down a query where you can skip three tables by name

select 'drop table if exists ' || tablename || ' cascade;' 
from pg_tables 
where schemaname = 'public' 
and tablename not in (:tbaleNamesToKeep);

Answer 3

I had 192 tables, so manually writing all tables out was infeasible. My strategy was to select all table names, remove the tables I wanted to keep and then run a DROP on the rest.

1. Select all tables

SELECT tablename FROM pg_tables

When you select, copy and paste the results, the text should look something like this:

"table1"
"table2"
"table3"
...

2. Remove the tables you want to keep

So let's say you want to keep table2, you just remove the line.

"table1"
"table3"
...

3. Add commas with text replacement

Replace "\n with ",\n. To do this, I use regexr.com, but any text editor will do.

"table1",
"table3",
...

4. Create and run the final PSQL statement

DROP TABLE
"table1",
"table3",
...