Postgres constraint function on a single column

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.

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:

• Disable all constraints and table checks while restoring a dump

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:

• Non-overlapping rectangles constrained to a boundary

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

StackExchange Database Administrators Q#123471, answer score: 4