Prevent drop column with index on it in PostgreSQL
Stack Exchange network consists of 183 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
Ask questions, find answers and collaborate at work with Stack Overflow for Teams. Explore Teams
In PostgreSQL when you drop column in a table, all indexes and constraints which use this column will be also dropped silently. If you overlook such situation, you can get serious problems in production.
Is it possible to make PostgreSQL return error when you drop a column with an index on? Or maybe some other ways to prevent described situation?
There is no way to do that except writing an event trigger that throws an exception whenever certain users try to run schema modifications.
You should make sure that any database user that connects and doesn't know better than randomly dropping columns without understanding the consequences does not own any database object and doesn't have the CREATE privilege on any schema.
