Postgresql – Prevent drop column with index on it in PostgreSQL

alter-tableconstraintindexpostgresql

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.

Here is a demonstration:

CREATE TABLE test (
    id INT     NOT NULL PRIMARY KEY,
    a  VARCHAR NOT NULL,
    b  VARCHAR NOT NULL
);
CREATE INDEX test_index ON test(a, b);

SELECT * FROM pg_indexes WHERE tablename='test';
/*
tablename | indexname
---------------------
test      | test_pkey
test      | test_index
*/ 

ALTER TABLE test DROP COLUMN b;
SELECT * FROM pg_indexes WHERE tablename='test';
/*
tablename | indexname
---------------------
test      | test_pkey
*/

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?

Best Answer

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.

Related Question