CODEWITHSUNDEEP
postgresql
pete.si
pete.si

Reputation: 103

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

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;

Upvotes: 8

Views: 23102

Answers (3)

Sacky San
Sacky San

Reputation: 1672

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);

Upvotes: 7

Chrisjan
Chrisjan

Reputation: 399

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",
...

Upvotes: 3

khampson
khampson

Reputation: 15356

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

Upvotes: 8

Related Questions