Is it possible to create a constraint in Postgres that will run a function, but only if a specific column is changed?

Right now, I have this:

ALTER TABLE public.employee
  ADD CONSTRAINT pin_is_unique_in_company CHECK 
     (fn_pin_is_unique_in_company(id, company_id, pin));

I only want this to run if the pin column has been modified.

  • why not use trigger in this case? – a_vlad Dec 12 '15 at 9:05
  • You would need to provide a lot more details to allow us to find the best answer. – Erwin Brandstetter Dec 12 '15 at 17:52

CHECK constraints should always evaluate to the same result. If your function is declared IMMUTABLE it's a perfect candidate, else there is more to discuss here. A NOT VALID constraint might be an option. Consider:

However, a CHECK constraint is run unconditionally. If you want to run the check only if a specific column has been changed, you need a different tool. One obvious candidate would be a trigger, like @a_vlad commented:

CREATE TRIGGER employee_upbef
BEFORE UPDATE OF pin ON public.employee
FOR EACH ROW EXECUTE PROCEDURE fn_pin_is_unique_in_company();

In Postgres 9.1 or later you can restrict the trigger to the change of specific columns.
You can't pass parameters per row to a trigger function, though. Instead reference the special row variable NEW inside the trigger function ...

Related example:

However, What you are trying to do sounds like a case for a multicolumn UNIQUE constraint. There is not enough information to tell.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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