To restore a single value from the JSONB logs for a given column and row the following indentifiers are necessary:

  1. A logged transaction ID you want to return to
  2. The audit_id to address the row (which implicitly defines the table)
  3. The (historic) column name
  4. The (historic) data type of the column
SELECT pgmemento.restore_value(10, 555, 'column_B', NULL::text);

Note: The last argument in pgmemento.restore_value is designed to be a template that is used in the function body and then returned to the user. That’s why it’s NULL in the beginning. It is of type anyelement. Thus, the returned data type is the same as the one you have chosen as the input.

If the column name and data type have changed over time you might need to check the audit_column_log table, to find out what has been the column name and data type before the given transaction id, e.g.

SELECT
  c.column_name,
  c.data_type
FROM
  pgmemento.audit_column_log c,
  pgmemento.audit_table_log t
WHERE
  c.audit_table_id = t.id
  AND c.txid_range @> 10::numeric
  AND t.table_name = 'table_A'
ORDER BY
  c.ordinal_position;

Restore only if changed

pgmemento.restore_value will always return a value as long as a log for this column is found the row_log table which has been inserted before or during the given transaction. To get a historic value that was inserted exactly at the given transaction call the pgmemento.restore_change function.

SELECT pgmemento.restore_change(10, 555, 'column_B', NULL::text);

This query will be faster as the whole history before the transaction does not have to be scanned. It is used for example to revert ALTER COLUMN events.