Enforce NOT NULL for set of columns with a CHECK constraint only for new rows I have a table and need to add a new column without a default value:

Constraint:

ALTER TABLE integrations.billables 
DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables,
ADD CONSTRAINT cc_at_least_one_mapping_needed_billables 
CHECK ((("qb_id" IS NOT NULL) :: INTEGER +
    ("xero_id" IS NOT NULL) :: INTEGER +
    ("freshbooks_id" IS NOT NULL) :: INTEGER +
    ("unleashed_id" IS NOT NULL) :: INTEGER +
    ("csv_data" IS NOT NULL) :: INTEGER +
    ("myob_id" IS NOT NULL) :: INTEGER) &gt; 0);


Column:

ALTER TABLE integrations.billables
DROP COLUMN IF EXISTS myob_id,
ADD COLUMN myob_id varchar(255);


Question:

How can I add the constraint for the next values and not for those which are already in there? (Otherwise I'd get the error check constraint "" is violated by some row).

This is related to my previous question: ERROR: check constraint is violated by some row
If you have a serial column or an integer one that's automatically populated with a nextval (so that you are never supposed to insert new rows with an explicit value for that column), you could additionally check whether the value of that column is greater than a specific value:

(
  (("qb_id" IS NOT NULL) :: INTEGER +
  ("xero_id" IS NOT NULL) :: INTEGER +
  ("freshbooks_id" IS NOT NULL) :: INTEGER +
  ("unleashed_id" IS NOT NULL) :: INTEGER +
  ("csv_data" IS NOT NULL) :: INTEGER +
  ("myob_id" IS NOT NULL) :: INTEGER) > 0
  OR
  YourSerialColumn &lt;= value
)

where the value should be determined as currval of the column/corresponding sequence at the time of altering/recreating the constraint.

This way the IS NOT NULL checks will apply only to the rows whose YourSerialColumn value is greater than value.

Note

This can be viewed as a variation on David Spillet's suggestion. The principal difference lies in the fact that this solution does not require structural changes (partitioning). Both options, however, rely on existence of a suitable column in your table. If there is no such a column and you can add it specifically to solve this problem, going with the partitioning idea might be the better option of these two.