Ograniczenie unikalności z zakresem dat

Rozważ pricestabelę z tymi kolumnami:

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

Chciałbym, aby baza danych egzekwowała zasadę, że produkt może mieć tylko jedną cenę w określonej ilości w zakresie dat (via where <date> BETWEEN start_date AND end_date).

Czy tego rodzaju ograniczenie oparte na zakresie jest wykonalne?

Tak, możesz użyć EXCLUDEograniczenia, które jest uogólnieniem UNIQUEograniczeń:

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
    );

Ograniczenie można interpretować jako powiedzenie:

Nie zezwalaj na dwa wiersze, które mają takie same product_id, takie same quantityi pokrywające się ( &&) zakresy dat.

Jest '[]'to dla poszukiwanego zakresu dat obejmującego wszystkie (domyślnie [)dla typów zakresów).

Zobacz dokumentację ograniczeń typów typów . Prawdopodobnie musisz także dodać rozszerzenie, uruchamiając (raz, dla każdej bazy danych, w której chcesz to zainstalować):

CREATE EXTENSION btree_gist;