Uniqueness constraint with date range

Question

Consider a prices table with these columns:

id         integer primary key
product_id integer -- foreign key
start_date date not null
end_date   date not null
quantity   integer
price      numeric

I'd like the database to enforce the rule that a product can only have one price at a specific quantity in a date range (via where <date> BETWEEN start_date AND end_date).

Is this kind of range-based constraint doable?

Answer 1

Yes, you can use an EXCLUDE constraint, which is a generalization of UNIQUE constraints:

ALTER TABLE prices 
  ADD CONSTRAINT unique_price_per_product_quantity_daterange
    EXCLUDE  USING gist
    ( product_id WITH =, 
      quantity WITH =, 
      daterange(start_date, end_date, '[]') WITH &&   -- this is the crucial
    );

The constraint can be interpreted as saying:

Don't allow two rows that have same product_id, same quantity and overlapping (&&) date ranges.

The '[]' is for the wanted all-inclusive date range (the default is [) for range types).

See the documentation on constraints on range types. You probably also need to add the extension by running (once, for each database where you want this installed):

CREATE EXTENSION btree_gist;