The pgmemento.audit_column_log table stores information about audited columns, which is important when restoring previous versions of tuples and tables. It contains the following columns: * id SERIAL: Primary Key * audit_table_id INTEGER NOT NULL: Foreign Key to pgmemento.audit_table_log * column_name TEXT NOT NULL: The name of the column * ordinal_position INTEGER: The ordinal position within the table * data_type TEXT: The column's data type (incl typemods) * column_default TEXT: The column's default expression * not_null BOOLEAN: A flag to tell, if the column is a NOT NULL column or not * txid_range numrange: Stores the transaction IDs when the column has been created and dropped

The txid_range column behaves in the same way like in the audit_table_log table. Lower boundary exclusive, upper boundary inclusive. When a column is ranamed or altered the range for the old version is closed and a new row is inserted. If the table is renamed there will also be new entries for all its columns in the audit_column_log because they need to reference to a new ID in audit_table_log. To trace different versions of the same column check the ordinal_position value.

Get historic column lists

When restoring historic records it's important to know the column name and data type for the requested time / transaction. You can use range operators like @> or && to filter for historic columns. pgMemento provides two functions that return a list of column names, data types and ordinal positions.

sql SELECT column_name, data_type, ordinal_position FROM pgmemento.get_column_list_by_txid(10, 'table_A', 'public');

When querying by a range of transaction IDs you have to use the table's log_id as input, as the name could have changed. You can retrieve if from the audit_table_log or by using the audit_table_check function. If some columns have changed by name or data type all the different versions will appear in the result set. To avoid name abiguity a counter is included into the result. The txid_range column is returned as well to allow for further filtering.

sql SELECT column_name, column_count, data_type, ordinal_position, txid_range FROM pgmemento.get_column_list_by_txid_range(1, 10, 1);