hmidi slim wrote: > To clarify the purpose of the table 'product_price_period': If I have a > product > and I choose period like [2018-05-01, 2018-05-31] and in the days_checked = > [0,2,3]. > The values of days_checked are the indexes of the week's day. > In this case 0 => sunday, 2 => tuesday, 3 => wednesday. > So the product is not product for every sunday and tuesday and wednesday in > the given period. > The problem with this design is when I make a select to fetch all the product > available for a given period, I have to generate all the dates of a given > period > and then eliminate the dates corresponding to days_checked and after that > return the products. > > E.g: > If I want to fetch all the products in a period of [2018-05-01, 2018-05-08] > > And considering that I have a list of products : > 1) product_id_1 [2018-04-01, 2018-05-05] [0,2] > 2) product_id_2 [2018-05-01, 2018-05-01] [2] > 3) product_id_3 [2018-04-01, 2018-05-17] [] > > The result wil be product_id_3.
This should be possible without using a temporary table. First filter out the products whose daterange does not contain your interval, then remove all products where generate_series(DATE '2018-05-01', DATE '2018-05-08') contains one of the forbidden week days. Maybe you should choose a simpler data model, like storing all allowed days for a product in an array (you can use a GIN index to speed up the <@ operator). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
