PolarDB:temporal_tables

Step 1: Install the extension

CREATE EXTENSION temporal_tables;

Step 2: Create the current table

CREATE TABLE employees
(
  name       text NOT NULL PRIMARY KEY,
  department text,
  salary     numeric(20, 2)
);

Step 3: Add the system period column

ALTER TABLE employees ADD COLUMN sys_period tstzrange NOT NULL;

Step 4: Create the history table

The easiest way to create a compatible history table is to use the LIKE clause:

CREATE TABLE employees_history (LIKE employees);

The history table does not need to be identical to the current table. For example, you can archive only a subset of columns, or add columns that track metadata such as the modifying user. The history table must satisfy two requirements:

1. It must contain a system period column with the same name and data type (tstzrange) as the current table.

2. Any column that exists in both tables must have the same data type.

Step 5: Create the versioning trigger

CREATE TRIGGER versioning_trigger
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE PROCEDURE versioning(
  'sys_period',        -- system period column name
  'employees_history', -- history table name
  true                 -- adjust: handle concurrent update conflicts automatically
);

`versioning()` parameters

Insert data

Insert rows the same way you would with any regular table:

INSERT INTO employees (name, department, salary)
VALUES ('Bernard Marx', 'Hatchery and Conditioning Centre', 10000);

INSERT INTO employees (name, department, salary)
VALUES ('Lenina Crowne', 'Hatchery and Conditioning Centre', 7000);

INSERT INTO employees (name, department, salary)
VALUES ('Helmholtz Watson', 'College of Emotional Engineering', 18500);

After inserting (assuming the statements ran on 2006-08-08), the current table looks like this:

name             | department                       | salary   | sys_period
-----------------+----------------------------------+----------+-----------------------------
Bernard Marx     | Hatchery and Conditioning Centre | 10000.00 | ["2006-08-08 00:00:00+00",)
Lenina Crowne    | Hatchery and Conditioning Centre |  7000.00 | ["2006-08-08 00:00:00+00",)
Helmholtz Watson | College of Emotional Engineering | 18500.00 | ["2006-08-08 00:00:00+00",)

The history table is empty — no changes have been made yet.

Update data

-- Updated on 2007-02-27
UPDATE employees SET salary = 11200 WHERE name = 'Bernard Marx';

After the update:

employees (current table)

name             | department                       | salary   | sys_period
-----------------+----------------------------------+----------+-----------------------------
Lenina Crowne    | Hatchery and Conditioning Centre |  7000.00 | ["2006-08-08 00:00:00+00",)
Helmholtz Watson | College of Emotional Engineering | 18500.00 | ["2006-08-08 00:00:00+00",)
Bernard Marx     | Hatchery and Conditioning Centre | 11200.00 | ["2007-02-27 00:00:00+00",)

employees_history

name         | department                       | salary   | sys_period
-------------+----------------------------------+----------+-----------------------------------------------------
Bernard Marx | Hatchery and Conditioning Centre | 10000.00 | ["2006-08-08 00:00:00+00","2007-02-27 00:00:00+00")

The archived row's sys_period is a closed interval — it records exactly when that version of the row was valid.

Delete data

-- Deleted on 2012-12-24
DELETE FROM employees WHERE name = 'Helmholtz Watson';

After the deletion:

employees (current table)

name          | department                       | salary   | sys_period
--------------+----------------------------------+----------+-----------------------------
Lenina Crowne | Hatchery and Conditioning Centre |  7000.00 | ["2006-08-08 00:00:00+00",)
Bernard Marx  | Hatchery and Conditioning Centre | 11200.00 | ["2007-02-27 00:00:00+00",)

employees_history

name             | department                       | salary   | sys_period
-----------------+----------------------------------+----------+-----------------------------------------------------
Bernard Marx     | Hatchery and Conditioning Centre | 10000.00 | ["2006-08-08 00:00:00+00","2007-02-27 00:00:00+00")
Helmholtz Watson | College of Emotional Engineering | 18500.00 | ["2006-08-08 00:00:00+00","2012-12-24 00:00:00+00")

Set a custom system time

By default, the trigger uses CURRENT_TIMESTAMP as the period start time. To use a different timestamp — for example, when migrating historical data from a source system that recorded its own timestamps — call set_system_time() at the start of the transaction:

SELECT set_system_time('1985-08-08 06:42:00+08');

To clear the custom time and revert to CURRENT_TIMESTAMP:

SELECT set_system_time(NULL);

Transaction behavior: if the transaction that called set_system_time() is rolled back, the custom time is discarded. If the transaction commits, the custom time applies for the rest of the session.

Create a history table using inheritance

As an alternative to the LIKE clause, create the history table first and then derive the current table from it using PostgreSQL table inheritance:

-- Create the history table with the full schema
CREATE TABLE employees_history
(
  name       text NOT NULL,
  department text,
  salary     numeric(20, 2),
  sys_period tstzrange NOT NULL
);

-- The current table inherits from the history table
CREATE TABLE employees (PRIMARY KEY(name)) INHERITS (employees_history);

Track who modified or deleted rows

Add triggers to record the session user on every change:

-- Record the user who inserted or updated a row
CREATE FUNCTION employees_modify()
RETURNS TRIGGER AS $$
BEGIN
  NEW.user_modified = SESSION_USER;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employees_modify
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE PROCEDURE employees_modify();

-- Record the user who deleted a row (fires when the trigger archives it to history)
CREATE FUNCTION employees_delete()
RETURNS TRIGGER AS $$
BEGIN
  NEW.user_deleted = SESSION_USER;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employees_delete
BEFORE INSERT ON employees_history
FOR EACH ROW EXECUTE PROCEDURE employees_delete();