3 ms·
There might not be a previous value. If you insert a record and then later delete it, you need to store a range or two timestamps to know when the insertion an
by perlgeek 3y ago
There might not be a previous value.
If you insert a record and then later delete it, you need to store a range or two timestamps to know when the insertion and the deletion happened.
- javier2 3y agoAh, I did not think of that!
- zeroimpl 3y agoThe way I deal with this is my history tables have 3 fixed columns: * OperationType: An ENUM of "INSERT", "UPDATE", "DELETE" * OperationTimestamp: TIMESTAMPTZ * OperationId: BIGSERIAL (needed to can handle cases where a row is modified multiple times in a transaction) This structure is super intuitive to understand, and very efficient since the History table is append-only. But if you are trying to use it to reconstruct the state of items from a specific time the past, it's a bit awkward and the use of a RANGE column instead would be easier to query.