Trigger vs. check constraint

Question

I want to add a field-level validation on a table. There is a field named "account_number" and this field should always pass a "luhn" check. I've found a function called "luhn_verify" that seems to work properly (google for it if you are interested). It returns a boolean. My question is:

Are there any major performance advantages in PostgreSQL for using a trigger for this validation vs. a check constraint.

Additional information:

• PostgreSQL 9.1
• Table does not currently have an insert trigger, but does have an update.

Disclaimers:

I feel like this has probably already been answered, but I can't seem to find a distinct answer. If so, please mark as duplicate and reference original question/answer.

Might be a better questions for the dba board.

Answer 1

The rule of thumb is to use CHECK constraint when possible.

A CHECK constraint is faster, simpler, more portable, needs less code and is less error prone. Triggers can easily be circumvented by other triggers, for instance.

A TRIGGER is more complicated. Use it when you have to, for more complex requirements.

If a CHECK constraint is too restrictive for your case or causes trouble reloading a dump, you could use the NOT VALID modifier as middle ground (Postgres 9.2+). And, optionally, VALIDATE it later. See:

• Disable all constraints and table checks while restoring a dump