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

sql 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.

sql 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.

sql 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.