Postgres constraint function on a single column

Question

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.

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

score 4 · Accepted Answer · edited Apr 13 '17 at 12:42

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.

Postgres constraint function on a single column

1 Answers1