How can i insert only rows to the view created

Question

IN Postgrsql, We have created a materialized view called- orders dataset which contains all the customer order details. Please suggest what is the ideal solution , if i just need to update only the current date and future orders of the customers daily and not the past orders. the past orders of the customers need not be refreshed daily. How shall i achieve this. I mean, only orders of current and future dates to be refreshed and past dates need not be changed/refreshed. How to do this, View / Materialized view.? What type of refresh will help me achieve this.

I tried creating materialized view.

Answer 1

In my understanding of your issue:

• You have a materialized view named orders
• This matview contains all customer orders (both past, current and future)
• you want to "update" the matview. I asume you mean refresh...
• your don't want to "update" (refresh) any old orders which have been finalized (probably because of performance)

To give a good answer on this we need to know what exactly the reason for the materialized view is (why is it not a table) and what the setup of the data in the m aterialized view is.

I can imagine a sollution in which you split the data from the materialilzed views in 2 materialized views. Namely all order from current month and onwards, and all orders from prev month and before. Then the "Archived" matview only needs to be refreshed at the start of a new month, and the daily refreshed matview will be performancewise massivly improved. To keep the same functionality you now have, you can put a view overtop the both of them which just unions them, which can then be called to get the same result.

Footnote: This is based on some assumptions about what you meant as OP since not everything in the question was clear.