CREATE TABLE tab ( useful_data int, more_data varchar, start timestamp GENERATED ALWAYS AS SYSTEM VERSION START, end timestamp GENERATED ALWAYS AS SYSTEM VERSION END ) WITH SYSTEM VERSIONING;(This hilariously verbose syntax arises because this is defined so that it fits into the more general generated columns feature, e. g.,
GENERATED ALWAYS AS IDENTITY, similar to PostgreSQL's serial type.)
INSERT INTO tab (useful_data, more_data) VALUES (...);This sets the "start" column to the current transaction timestamp, and the "end" column to the highest possible timestamp value.
UPDATE tab SET useful_data = something WHERE more_data = whatever;For each row that would normally be updated, set the "end" timestamp to the current transaction timestamp, and insert a new row with the "start" timestamp set to the current transaction timestamp.
SELECT * FROM tab;This only shows rows where
current_timestampis between "start" and "end". To show the non-current data, the following options are
SELECT * FROM tab AS OF SYSTEM TIME timestamp; SELECT * FROM tab VERSIONS BEFORE SYSTEM TIME timestamp; SELECT * FROM tab VERSIONS AFTER SYSTEM TIME timestamp; SELECT * FROM tab VERSIONS BETWEEN SYSTEM TIME timestamp AND timestamp;There's also the option of
CREATE TABLE tab ( ... ) WITH SYSTEM VERSIONING KEEP VERSIONS FOR interval;to automatically delete old versions.
That's more or less it. It's pretty much xmin/xmax/vacuum on a higher level with timestamps instead of numbers. And it's a revival of the old time travel feature. Obviously, you can do most or all of this with triggers already.