Constraints that prevent e.g. overlapping ranges

create table product_price
(
   price_id      serial        not null primary key,
   product_id    integer       not null references products,
   price         numeric(16,4) not null,
   valid_during  daterange not null       -- defines the time span when this price is/was valid
);

-- this constraint will ensure that two ranges for one product_id do not overlap
alter table product_price
  add constraint check_price_range
  exclude using gist (product_id with =, valid_during with &&)
  
-- this works:
insert into product_price
  (product_id, price, valid_during)
values
  (1, 100.0, '[2010-01-01,2011-01-01)'),
  (1,  90.0, '[2011-01-01,)');
  
-- but this fails, because the valid date range for the price of 80.
-- conflicts with the interval defined for the price of 90.0
insert into product_price
  (product_id, price, valid_during)
values
  (1,  80.0, '[2012-01-01,2013-01-01)');

Back to the SQL Feature Comparison