Uniqueness constraint with date range 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 &lt;date&gt; BETWEEN start_date AND end_date). 

Is this kind of range-based constraint doable?
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 &amp;&amp;   -- 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 (&amp;&amp;) 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;