Ability to populate history table with insert
Opened this issue · 6 comments
Hey,
I'd like history table to contain all the data, not only modified rows. Would it be possible to add another parameter to temporal_tables, so even an INSERT to original table creates a row in history table?
Actually I don' even want to add sys_period to original table. I just want history table to automatically track any changes in the original table.
Yes, it is possible. Actually, that was also my first idea of how to implement system versioning, but not how the people who wrote the SQL standard decided it should be implemented. I will see if I can come up with something over this weekend.
You could take a look at chronomodel that uses similar approach: https://github.com/ifad/chronomodel They just version whole schema, not only a table, and it's not as integrated with postgres as temporal_tables
This turned out to be more difficult than I expected, so I gave it up after some time. The worst thing about this approach is that when you insert a new record into the table and then update that record in the same transaction, you have to find the history record you inserted before in order to update it. So, you must have a unique constraint in the original table and the corresponding index (which includes the system_period column in addition to the columns of the unique constraint of the original table).
I might be late for the party, but doesn't table inheritance as described in readme solve the original problem? You'll get all records including current ones.
I think the main point was to get rid of the sys_period column in the original table. Unfortunately, this approach comes at the price of performance loss since you will have to add two additional indices to the original and history tables and check the existence of a record any time you update it.