Deal with dependencies error messages when dropping tables

Question

I need to drop tables that are not used somewhere else in my database (mainly views). I have listed a series of DROP TABLE... commands but I can't run them at once because an error message is issued each time a used table is found. I would need a command that ignore such messages (and then do not remove the used tables) and jump to the next command.

For instance:

DROP TABLE schema1.table1 ;
DROP TABLE schema1.table2 ;
DROP TABLE schema1.table3 ;
DROP TABLE schema1.table4 ;
DROP TABLE schema1.table5 ;

In this example, say table1 and table3 are used in views whereas tables 2, 4 and 5 are not. If I run the script, I have an error message for table1. I have then to comment the line and run the script again. Then, table2 is dropped properly and I get an error message again for table3. I then have to comment this line and run the script again. I eventually get the 2 last tables dropped.

The point is that I have hundreds of such tables, used and not used mixed together...

I guess you need this`DROP TABLE schema1.table1 CASCADE`, `CASECADE`- _Automatically drop objects that depend on the table (such as views)_.. What's your error message?? — Vivek S., Nov 24 '15 at 08:08
I couldn't find any "continue on error" option for pgAdmin. However, I think this is the default behaviour for the psql client, which you can launch from pgAdmin's "Plugins" menu. From psql, you can run your script with `\i '/path/to/script.sql'`. — Nick Barnes, Nov 24 '15 at 08:52
Maybe use pgscript (I wrote plsql but i think its for oracle), create a method/function that execute all these method in a loop with exception error handling each call (so when an exception is thrown, you can carry on). Can't give you more detail as I'm not good at it. — Asoub, Nov 24 '15 at 10:31
@Nick Barnes: ...but it worked fine without `\i`option! Thanks a lot for putting me on psql client way. — wiltomap, Nov 24 '15 at 10:48

score 0 · Answer 1 · answered Nov 24 '15 at 10:43

It's plpgsql actualy (I wrote plsql in comments).

There is an example on http://www.jaredlog.com/?p=137

Just replace "EXCEPTION WHEN UNDEFINED_TABLE THEN" in the function with the exception's name thrown when you try to execute it and it should work.

I think you can even remove the "WHEN UNDEFINED_TABLE" so it catches all exceptions.

Use this function with your table names as parameter (not the whole query).

score 0 · Answer 2 · answered Nov 24 '15 at 10:58

Thanks to @Nick Barnes comment, I found a simplier way to do so:

Create a SQL file with this content:

\set ON_ERROR_STOP 0

DROP TABLE schema1.table1;
DROP TABLE schema1.table2;    
DROP TABLE schema1.table3;
DROP TABLE schema1.table4;
DROP TABLE schema1.table5;

Call this file through psql client:
```
'D:/myFile.sql'
```

This drops table1 and table3!

Deal with dependencies error messages when dropping tables

2 Answers2