0

I have a Postgres server with 2 databases on it, db1 and db2. The second one, db2 has lots of tables in it.

I want to keep both of these databases, but would like to "truncate" (delete all tables from) db2. My best attempt thus far is:

db2=# \c db1
You are now connected to database "db1" as user "postgres".
db1=# SELECT 'drop table if exists "' || tablename || '" cascade;' as pg_drop FROM pg_tables WHERE schemaname='db2';

postgres=# \c db2
You are now connected to database "db2" as user "postgres".
db2=# \dt
 public | agent_machines                | table | muyapp_admin
 public | agent_message_queue           | table | muyapp_admin
 public | agent_pools                   | table | muyapp_admin
 public | alerts                        | table | muyapp_admin
 ...

And clearly, its not working, since there are still tables in db2 after I do the SELECT.

Can anyone spot where I'm going awry?

simplezarg
  • 168
  • 1
  • 9
  • First: you are running a `SELECT` statement - that will never drop anything. And a schema is something different than a databases. If your database `db1` does not contain a **schema** named `db2` then obviously the select doesn't return anything. And even if your query returned something **and** you used `\gexec` after the SELECT statement to run those statements that would have dropped (not "deleted") all tables in `db1` as you connect to that database. The tables in the other database are completely unaffected. I think you are confusing schema with database. –  Sep 16 '21 at 20:07
  • OK so what exact command(s) do I need to run so that, afterwards, `db2` still exists, but is completely empty? – simplezarg Sep 16 '21 at 20:10
  • Well, your SELECT is OK, but it needs to be run in `db2` not `db1` and you apparently want to get rid of the restriction on the schema. Once your SELECT returns the correct list of statements, you can use `\gexec` to execute that. But it all needs to be done while connected to `db2` as you want to drop the tables _there_. –  Sep 16 '21 at 20:11
  • OK Thanks, but just to be clear, I could care less about the schema. I'm only going based on examples I've patched together from examples on the internet. All I care about is that `db2` is empty, but that all the users that have `GRANTs` to use the `db2` database remain in place. If ignorance is bliss, I could care less about any schema (if that helps you clue me in any better!). – simplezarg Sep 16 '21 at 20:23
  • so you'd need to connect to `db2`, then run your `SELECT` statement, then copy the sql commands it returns and paste them into the terminal. I assume you're trying to follow https://stackoverflow.com/questions/3327312/how-can-i-drop-all-the-tables-in-a-postgresql-database?rq=1 – melcher Sep 16 '21 at 21:11
  • OK so the only problem there is, look at the `SELECT` statement I have above. As soon as I enter it, I get a blank space (empty line) directly beneath it. So there's no actual SQL commands being returned there. Or are you saying that if I was connected to `db2`, there would be? – simplezarg Sep 16 '21 at 21:15
  • Obviously you have no tables in the `db2` **schema** - if you don't care about the schema, then remove the WHERE clause from your SELECT statement. –  Sep 16 '21 at 21:39

1 Answers1

1

If you want to drop tables in the database db2 then you need to connect to that database. If you want to drop the tables regardless of the schema they are stored in, remove the WHERE clause that limits the list of tables to that specific schema (do you really have a schema named db2 in a database named db2 as well?)

You also need to include the schema name in the drop statement. Typically dynamic SQL is best generated using the format() function:

postgres=# \c db2
You are now connected to database "db2" as user "postgres".
postgres=# SELECT format('drop table %I.%I cascade;', schemaname, tablename)
FROM pg_tables 
WHERE schemaname not in ('pg_catalog', 'information_schema');

If you are satisfied with the result of that query, then end the statement with \gexec instead of the ; which will make psql run the result of that query as a script - which will run each DROP statement.