Can I stop DROP COLUMN from succeeding on particular tables in PostgreSQL 9 or 10 or later?

My application makes some assumptions about columns being forever, not disappearing. I'm afraid of later programmers forgetting about this constraint.

I know triggers can fire to control INSERT, MODIFY, and DELETE of rows. But what about DDL? Can I protect against certain changes to the table structure?

Security might be one way, assigning permissions to users and roles. But these future programmers will be allowed to add columns, only dropping columns is forbidden.

I am not concerned about malicious activity. I'm defending against the eager-beavers who may move too fast to remember the bigger picture.

  • 2
    If you are afraid that programmer will forget that certain columns are important, make sure you have proper integration tests. And if they can drop random columns without knowing what they are doing then they will find a way to work around whatever "protection" you come up with
    – user1822
    Jun 30, 2017 at 20:36
  • @a_horse_with_no_name Fair point. Certainly I will have meta-data stored that can will be used to verify table integrity. Nevertheless, if I can stop the errant programmer at the source, that would be nice. I am not concerned about malicious activity, I'm just defending against the clever eager-beavers who may move too fast to remember the bigger picture. Jun 30, 2017 at 20:40

1 Answer 1


Event Triggers

There are event triggers to control DDLs, trapping for the event sql_drop.

Call CREATE EVENT TRIGGER. In the trigger function, check to see if the object being dropped is one of your important columns. If so, raise exception to roll-back transaction.

The sql_drop event occurs just before the ddl_command_end event trigger for any operation that drops database objects. To list the objects that have been dropped, use the set-returning function pg_event_trigger_dropped_objects() from the sql_drop event trigger code (see Section 9.28). Note that the trigger is executed after the objects have been deleted from the system catalogs, so it's not possible to look them up anymore.


drop table if exists t;
drop event trigger if exists etg;
drop function if exists fetg();

create function fetg() returns event_trigger language plpgsql as $$
  if exists (
    select 1
    from pg_event_trigger_dropped_objects() as t
      t.object_type = 'table column' and
      t.object_identity like any(array['%.t.y', 'public.tt.zz']))
    raise exception 'Columns t.y and public.tt.zz are important!';
  end if;
end $$;

create event trigger etg on sql_drop execute procedure fetg();

create table t(x int, y int);
alter table t drop column y;

More docs:

  • 1
    Went above and over in this one. =) Awesome answer. Jul 1, 2017 at 0:26
  • 2
    @EvanCarroll Everything is simple if you know where to dig :o)
    – Abelisto
    Jul 1, 2017 at 0:38
  • Available from PostgreSQL 9.4.
    – bpelhos
    Jan 17, 2019 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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