PostgreSQL : NOT VALID constraint vs validation trigger

ChennyStar

Jul 18

0 views1

I have a table with a "currency" column. I'd like to prevent further insertion of certain currencies, without removing the existing ones.

Initially I was thinking of a validation trigger.

Then I discovered the NOT VALID option of ALTER TABLE ADD CONSTRAINT, which prevents the validation of existing data.

The documentation seems to imply that the NOT VALID option is mostly for performance reasons, to allow the user to defer the validation until later. But nowhere it says explicitly that it can not be used for the purpose of keeping old (invalid) data.

In this post, the guy says : "You can even leave the constraint in a NOT VALID state, since that better reflects what it actually does: check new rows, but give no guarantees for existing data. There is nothing wrong with a NOT VALID check constraint."

So, can the NOT VALID option be used for this purpose ? Or would it be better to make a validation trigger ?

database-design database-trigger constraints

postgresql

1 answer

Accepted answer · original discussion

user7070613

Jul 18

Permalink

The part the quote missed is this:

https://www.postgresql.org/docs/current/sql-altertable.html

"The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys, or they'll fail unless the new row matches the specified check condition). "

So if someone updates the old rows again the constraint will be applied. NOT VALID is just a way to spread the validation out over time. If you want to maintain multiple states of validation on the column you will need a trigger to do that on whatever criteria you select.

Your perspective belongs here.

1 question comment

Use comments to ask for clarification. Post a solution as an answer.

PigeoPermalink

May 13

FYI, the documentation now explicitly says that NOT VALID can be used "in cases where the table is known to contain pre-existing violations", and that "Once the constraint is in place, no new violations can be inserted, and the existing problems can be corrected at leisure until VALIDATE CONSTRAINT finally succeeds."