14 ms·
The key to temporal tables in Postgres is the following: • add a timestamp range column • create a history table that matches the main table • using table in
by ttfkam 4y ago
The key to temporal tables in Postgres is the following:
• add a timestamp range column
• create a history table that matches the main table
• using table inheritance, have the live table inherit from the history table
At this point all queries on the main table are "live" and queries on the history include past and present data.
• add an insert trigger that initializes the live table's timestamp range to start "now()"
• add AFTER UPDATE/DELETE/TRUNCATE triggers to insert the OLD record into the history table with appropriate timestamp ranges
Bonus
• have event triggers automatically set up the temporal tables on CREATE TABLE
- eyelidlessness 4y agoSo for temporal data this is a pretty solid approach. For bitemporal data, it’s more complicated to do in plain SQL (though it can be done). To clarify: I’m using temporal here the same way you are, as an immutable/append-only record of state as it existed at the time. Whereas bitemporal data has that property plus a separate historical record of when data was/should have been/should be or optimistically will be effectively true. The thing which helped me grasp this was correcting historical accounting according to the domain (schema), eg updating meeting records. You of course want to preserve the previous record upon update, but the “current” state of what was decided a week ago might still change.