Adding checks in postgres table where data is already incorrect

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.

Answer

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.