Overlap Free Time Slices

table product

The first row of the table means the price of the product with product_id = 1002 is $100 from 2021-07-01 to 2021-07-04, including the day 2021-07-04.

For 2021-07-09, the second row states a price of $105 and the third row a price of $103. This situation is not allowed for temporal tables. Time slices must be free of overlap for each key.

PostgreSQL code to determine any time slice that overlaps with other time slices:

with prd as
   select *, 
         over(partition by product_id order by valid_from, valid_to)
         as rownr
   from product
select * 
from prd a join prd b using(product_id)
where a.rownr < b.rownr and
     (a.valid_from, a.valid_to) overlaps (b.valid_from, b.valid_to);
%d bloggers like this: