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.
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')
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.