Enforce NOT NULL for set of columns with a CHECK constraint only for new rows

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 <= 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.

Just add the constraint as NOT VALID

From the manual:

If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, [...] and they'll fail unless the new row matches the specified check constraints)

Mark all your existing rows as old:

ALTER TABLE integrations.billables
ADD COLUMN is_old BOOLEAN NOT NULL DEFAULT false;

UPDATE integrations.billables SET is_old = true;

And set up the constraint to ignore old rows:

ALTER TABLE integrations.billables
ADD CONSTRAINT cc_at_least_one_mapping_needed_billables 
CHECK (
    NOT(("qb_id", "xero_id", "freshbooks_id", "unleashed_id", "csv_data", "myob_id") IS NULL)
    OR is_old
);

(Yes, that IS NULL check works. See here.)

Advantages of this mechanism:

• Constraint remains valid
• You can continue to update old rows without filling in this value

Downsides:

• A similar situation down the road will be messy. You would have to add a second boolean column or some other hoop jumping for the second new column.
• If you want to force updated rows to be given a value, this won't do it.
• This has the potential for abuse, since someone could just flip the is_old flag to true. (This can be addressed, though. See below.) This isn't something to be concerned about if end users can't access the database directly and you can trust the developers not to do wacky things with the data.

If you are worried about somebody changing the flag, you could set up a trigger to prevent any inserts or updates from setting is_old to true:

CREATE FUNCTION throw_error_on_illegal_old()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  BEGIN
    IF NEW.is_old THEN
      -- Need to make sure we don't try to access
      -- OLD in an INSERT
      IF TG_OP = 'INSERT' THEN
        RAISE 'Cannot create new with is_old = true';
      ELSE
        IF NOT OLD.is_old THEN
          RAISE 'Cannot change is_old from false to true';
        END IF;
      END IF;
    END IF;
    -- If we get here, all tests passed
    RETURN NEW;
  END
  $$
;

CREATE TRIGGER billables_prohibit_marking_row_old
BEFORE INSERT OR UPDATE ON integrations.billables
FOR EACH ROW EXECUTE PROCEDURE throw_error_on_illegal_old()
;

You do still have to trust that no one who can modify the database schema is going to come along and drop your trigger or something, but if they're going to do that, they could drop the constraint, too.

Here's a SQLFiddle demo. Note that the "should skip" row is not in the output (as we desired).