Is it possible to have postresql restrict/prevent an update on a specific record if the update includes changes to specific columns?
How would this be implemented. A trigger/constraint? What would be the most efficient way to implement this?
I am using version 9.1
The easiest way is to create BEFORE UPDATE trigger that will compare OLD and NEW row and RAISE EXCEPTION if the change to the row is forbidden.
No, but it should be pretty trivial to write.
Just set up a BEFORE UPDATE trigger that compares old field against new field and does a RAISE ERROR if they're different.
The pgSQL docs have a few examples of how to write a trigger function.
Several solutions are available:
You can use a BEFORE UPDATE trigger, as already proposed in other answers. Another Stack Overflow answer shows an example for such a trigger.
You can split your table into two tables, with a 1:1 relationship between them. Then, you can provide SELECT and UPDATE access to one table and only SELECT access to the other.
You can provide UPDATE access to only a subset of columns of the table: GRANT UPDATE(col1, col2) (details).
You can provide read-only access to the table and create a VIEW with the updateable columns of the table, and grant UPDATE access to that.
You can hide the table behind a FUNCTION using SECURITY DEFINER. The table itself would not provide UPDATE access, instead users can only update the table through the function.
Inspired by this list. See also a more detailed look at these solutions in another answer of mine, in the context of the Supabase system.
