CODEWITHSUNDEEP
postgresql
simplezarg
simplezarg

Reputation: 68

Select drop table if exists from PSQL truncation trick is not working

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?

Upvotes: 0

Views: 684

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions