2

I have a lot of tables in a PostGIS-Database and I want to delete most of them. In the QGIS DB manager, I am able to delete one by one (right-click - delete). However, that's quite annoying. How to delete several tables in the same time.

1

You should use DBeaver to administer your PostGIS/PostgreSQL database... DB Manager is nice, but since you have the database, you should use a more robust and proper tool.

With DBeaver, since you don't want to delete all your tables, you should be wary of using any SQL script that attempts to delete tables in bulk - that is dangerous. (There are, however, ways to isolate tables using SQL scripts, but again, be very careful).

My preferred way is to use DBeaver.

Here, you can open a schema, view the tables, interactively select which ones you want to delete, and right-click > delete the tables. This is a safe way to accomplish a bulk delete.

enter image description here

  • Wonderful, thanks for the answer. So I now have two working solutions, more than I expected. Thanx to both contributors, appreciate it a lot! – babel Jun 5 at 20:15
  • @babel good stuff! – DPSSpatial Jun 5 at 20:40
2

By being very cautious and aware that this will erase all tables and their configurations, triggers, attached views, you can execute the following SQL, then copy the result and execute it:

SELECT 'DROP TABLE IF EXISTS ' || table_schema || '.' || table_name || ' CASCADE;' AS drop_script
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name NOT IN ('spatial_ref_sys');

You can adapt the script in the WHERE clause to :

WHERE table_name IN ('my_table_to_erase1', 'my_table_to_erase2')
  • I get the following syntax error: syntax error at or near "'.'" (LINE 1) , ^ – babel Jun 5 at 9:58
  • I checked again my code and it works perfectly ... – J. Monticolo Jun 5 at 10:05
  • In case my schema has the name table_schema and the table table_name, then how exactly is the implemenation, without the spacholders like ||? I tried it like this and got the error: SELECT 'DROP TABLE IF EXISTS ' table_schema'.' table_name ' CASCADE;' AS drop_script FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema') AND table_name NOT IN ('spatial_ref_sys'); – babel Jun 5 at 10:16
  • the pipes | are for concatenation between many data, here between text and database values. If you delete it, it won't work. – J. Monticolo Jun 5 at 11:09
  • OK, sorry, of course! I considered the pipes as a kind of wildcard - as you see, I'm a bloody beginner, so thanks for you patience! Now, it, works perfect. – babel Jun 5 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.