PostgreSQL exclusion constraints in a bitemporal setting?

Solution 1:

Just list both ranges in the exclusion constraint.

ALTER TABLE tbl ADD CONSTRAINT foo
EXCLUDE USING gist (attribute_1 WITH =, attribute_2 WITH =  -- more?
                  , tsrange(valid_from, valid_to) WITH &&
                  , tsrange(registration_from, registration_to) WITH &&);

It should be safe to assume that essential basics are clear after I answered your related (simpler) question with more explanation a couple of weeks ago. Others may want to read this first:

• How to ensure entries with non-overlapping time ranges?

To enforce your constraint, the order of expressions doesn't even matter. Consider the basic definition in the manual of how exclusion constraints operate:

Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null.

This effectively enforces your constraint: only if all expressions evaluate to true, in other words, both ranges overlap and all attributes match exactly, the constraint raises an exception.

However, since the constraint is implemented with the use of a corresponding multicolumn GiST index, the order of expressions matters for performance after all. The manual:

A multicolumn GiST index can be used with query conditions that involve any subset of the index's columns. Conditions on additional columns restrict the entries returned by the index, but the condition on the first column is the most important one for determining how much of the index needs to be scanned. A GiST index will be relatively ineffective if its first column has only a few distinct values, even if there are many distinct values in additional columns.

So rearrange expressions to place the ones with the most distinct values in the column first.