How to store table history in PostgreSQL

Question

Scenario

We need to store record history in PostgreSQL such that when a record is inserted into or updated in a primary table (e.g: pets), it is automatically backed up to a history table (pets_history).

Ideally we need the history table to be generated based on the schema of the primary table without any human intervention.

INSERT INTO pets(name, species) VALUES ('Meowth', 'Cat')

pets:
+---+------------+-------------+
|id | name       | species     |
+---+------------+-------------+
| 1 | Meowth     | Cat         |
+---+------------+-------------+

A Trigger should automatically insert the record into pets_history:

pets_history:
+----+--------+-----------+---------+
| id | ref_id | name      | species |
+----+--------+-----------+---------+
| 1  | 1      | Meowth    | Cat     |
+----+--------+-----------+---------+

When an update is made to pets to change the name of my cat from Meowth to Persian. e.g:

UPDATE pets SET name = 'Persian' WHERE id = 1;

pets:
+---+------------+-------------+
|id | name       | species     |
+---+------------+-------------+
| 1 | Persian    | Cat         |
+---+------------+-------------+

I would like to end up with the following...

pets_history:
+----+--------+-----------+---------+
| id | ref_id | name      | species |
+----+--------+-----------+---------+
| 1  | 1      | Meowth    | Cat     |
| 2  | 1      | Persian   | Cat     |
+----+--------+-----------+---------+

Later on when another column/field is added to the pets table, e.g: color

pets:
+---+--------+---------+-------+
|id | name   | species | color |
+---+--------+---------+-------+
| 1 | Meowth | Cat     | cream |
+---+--------+---------+-------+

we want this to be reflected in the pets_history table automatically:

pets_history:
+----+--------+---------+---------+-------+
| id | ref_id | name    | species | color |
+----+--------+---------+---------+-------+
| 1  | 1      | Meowth  | Cat     | null  |
| 2  | 1      | Persian | Cat     | null  |
| 3  | 1      | Persian | Cat     | cream |
+----+--------+---------+---------+-------+

If anyone knows any way of doing this natively in PostgreSQL or otherwise then please share. We had a look at this question/answer Implementing history of PostgreSQL table which partially solves challenge, but it does not auto-create the _history table.

Answer 1

You can use to_jsonb to keep whole row as JSON object in history table. In this case, you don't need to care about adding new columns in history table, because key of value will be column name.

Pets table

CREATE TABLE public.pets
(
  id serial NOT NULL,
  name text,
  species text,
  PRIMARY KEY (id)
);

Pets history table

CREATE TABLE public.h_pets
(
  id serial NOT NULL,
  target_row_id integer NOT NULL,
  executed_operation integer NOT NULL,
  operation_executed_at timestamp without time zone NOT NULL DEFAULT now(),
  data_after_executed_operation jsonb,
  PRIMARY KEY (id)
);

Function for adding row to history table

CREATE OR REPLACE FUNCTION public.on_content_change()
  RETURNS trigger
  LANGUAGE 'plpgsql'
AS $BODY$
  DECLARE
    target_history_table TEXT;
  BEGIN
    target_history_table := TG_ARGV[0];

    IF TG_OP = 'INSERT'
    THEN
      EXECUTE
        format(
          'INSERT INTO %I (target_row_id, executed_operation, data_after_executed_operation) VALUES ($1.id, 0, to_jsonb($1))',
          target_history_table
        )
        USING NEW;
      RETURN NEW;
    ELSIF TG_OP = 'UPDATE'
    THEN
      EXECUTE
        format(
          'INSERT INTO %I (target_row_id, executed_operation, data_after_executed_operation) VALUES ($1.id, 1, to_jsonb($1))',
          target_history_table
        )
        USING NEW;
      RETURN NEW;
    ELSIF TG_OP = 'DELETE'
    THEN
      EXECUTE
        format(
          'INSERT INTO %I (target_row_id, executed_operation) VALUES ($1.id, 2)',
          target_history_table
        )
        USING OLD;
      RETURN OLD;
    END IF;
  END;
$BODY$;

And trigger for pets table

CREATE TRIGGER pets_history_trigger
  BEFORE INSERT OR DELETE OR UPDATE
  ON public.pets
  FOR EACH ROW
  EXECUTE PROCEDURE public.on_content_change('h_pets');