Overlap Free Time Slices

product_id	valid_from	valid_to	price
1002	2021-07-01	2021-07-05	100
1002	2021-07-05	2021-07-10	105
1002	2019-07-09	9999-12-31	103

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 *, 
       row_number()
         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);