Creating a cumulative "collection" for each month in PSQL?
I want to create a trigger or some kind of event over a PSQL database that will run at the end of the month and store data from one table (holds data for each employee when he came and returned home from work every day) inside some collection (database ) tables, so I can still use this data if I'm doing monthly reports (PSQL is a database, C # is used to build an app). Any idea how to do this?
source to share
If you do not need to separate this data very urgently, just leave it in the tables where it is. You usually don't need to archive old data in a database, just write queries that fetch data generated since the beginning of the month, etc.
If you have to archive and summarize the process, use a cron or task scheduler that calls the plpgsql routine to execute the appropriate queries INSERT INTO ... SELECT
and DELETE FROM
for your tables. There is no general way to do this.
If you are looking for table splitting, there are tools like pgpartman for that. But you probably won't need this if you tweak your queries / indexes a bit.
source to share