15

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?

23

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;
  • This is awesome. I don't think daterange is exactly the same since it's exclusive lower bound, but that's easy to fix. Should I really be migrating my data to use a daterange column type (can make that a separate question if that's better) or is this two-column thing reasonable? – spike Aug 10 '15 at 15:57
  • The default in inclusive lower bound and exclusive upper bound, if I remember well. I'll edit for the all-inclusive. I usually prefer the default as it's common in hotel-like applications. (I get in the hotel at 2nd, I get off at 8th, stayed 6 days. The next occupier can come in at 8th) – ypercubeᵀᴹ Aug 10 '15 at 15:59
  • I might actually be flipped on which is which... just found out about range types today and I'm reading the docs! – spike Aug 10 '15 at 16:00
  • I'm not sure about what is preferable, 2 columns or one with daterange. You can ask a separate question. It will probably depend on the usage you want, queries, ease of use (and index needs). If there are separate columns, it would easier for example to have an index on (product_id, start_date). With a daterange, that would have to be an index on (product_id, lower(range_column)) – ypercubeᵀᴹ Aug 10 '15 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.