Adding checks in postgres table where data is already incorrect

Question

This seemed to be a useful question to me and didn't find any written information about it anywhere so thought that this might be too obvious. Just want to confirm this:

If I alter a postgres table to add a check but the data that is already present is inconsistent with this check, nothing will happen to it, right? Postgres will check only the data that is inserted/updated after the check is added?

Suppose some row have birthyear column value as 1987. What if I add a check as birthyear > 1990 and I don't update this field ever but I update some other field in this row. Will I be able to update this row? If not, then is there a way to add a check in a way so that I will be able to update all the old rows without the check but newly added rows are effected, basically adding the check on insert queries only and not on update queries? Hope my question is clear to understand.

score 3 · Accepted Answer · answered Jun 16 '20 at 23:17

A check constraint does check the values already in the table and cannot be created if there are rows violating the condition. That's the point of a constraint, assert that the data meets the condition.

What you could do to just prevent new data to violate the condition is creating a trigger that checks it and throws an error if it is violated. Something along the lines of:

CREATE FUNCTION check_birthyear
                ()
                RETURNS TRIGGER
AS
$$
BEGIN
  IF NOT new.birthyear > 1990 THEN
    RAISE EXCEPTION 'birthyear must be greater than 1990';
  END IF;

  RETURN new;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER elbat_check_birthyear
               BEFORE INSERT
               ON elbat
               FOR EACH ROW
               EXECUTE PROCEDURE check_birthyear();

db<>fiddle

But you should really think about it again. Asking yourself if you really want that old data violation the condition. It may be better to just clean up the old and faulty data than keep it. You can never be sure of the condition a row meets otherwise.

This answers my question and thanks for the advice. – as2d3 Jun 16 '20 at 23:27 — as2d3, Jun 16 '20 at 23:27

score 1 · Answer 2 · answered Jun 17 '20 at 06:08

PostgreSQL doesn't know an old row from a new row, so you cannot get a constraint that allows violations only for old rows.

As has been explained in the other answer, existing rows are checked for validity if you create a constraint.

There is one exception that would allow you to create a CHECK constraint on a table that contains rows that violate the condition: create the constraint as NOT VALID. Then existing rows won't be checked for compliance.

But, as said in the beginning, modifications of old rows would only be allowed if the new row version satisfies the condition.

An alternative idea would be to add a new column old of type boolean to distinguish old rows from new ones. Then you can explicitly except old rows in the check condition.

Adding checks in postgres table where data is already incorrect

2 Answers2