PostgreSQL : NOT VALID constraint vs validation trigger

Question

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 ?

Sexivalent

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." – Pare

Answer 1

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.

Resnatron