Add column to tables that tracks datetime when edited most recently, last_updated_time

[zipperer]

A possible alternative for implementing this: https://dba.stackexchange.com/questions/233735/track-all-modifications-to-a-postgresql-table

[zipperer]

A possible alternative for implementing this: https://dba.stackexchange.com/questions/233735/track-all-modifications-to-a-postgresql-table

I record in this directory notes on that alternative.

[zipperer]

On 20231219, we created a database

CREATE DATABASE example_db;
psql> \connect example_db

created a table with a column mod_timestamp that postgres cluster will update automatically on each edit

CREATE TABLE table_with_column_that_tracks_modification_date (
  record_id SERIAL,  
  creation_timestamp TIMESTAMP DEFAULT NOW(),
  mod_timestamp TIMESTAMP DEFAULT NOW(),
  notes VARCHAR, 
  other TEXT); -- used TEXT rather than VARCHAR only to show similar type that we will encounter elsewhere

inspected the table structure and rows

psql> \d table_with_column_that_tracks_modification_date
SELECT * FROM table_with_column_that_tracks_modification_date;

inserted into the table

INSERT INTO table_with_column_that_tracks_modification_date (notes, other) VALUES ('test notes', 'test other');

SELECT * FROM table_with_column_that_tracks_modification_date;

updated a row in the table and observed that mod_timestamp was not automatically updated along with the edit

UPDATE table_with_column_that_tracks_modification_date SET other = 'retest other' WHERE other = 'test other';

SELECT * FROM table_with_column_that_tracks_modification_date; -- observe mod_timestamp has not been updated

created a function and trigger that will automatically update mod_timestamp along with the edit

CREATE FUNCTION update_column_with_modification_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.mod_timestamp = now();                                                                                                                                                                                                    
RETURN NEW;                                                                                                                                                                                                                            
END;                                                                                                                                                                                                                                   
$$ language 'plpgsql';

CREATE TRIGGER update_column_with_modification_timestamp_trigger
BEFORE INSERT OR UPDATE OR DELETE ON table_with_column_that_tracks_modification_date
FOR EACH ROW EXECUTE PROCEDURE update_column_with_modification_timestamp();

updated a row in the table again and observed that mod_timestamp was updated

UPDATE table_with_column_that_tracks_modification_date SET other = 'retest another' WHERE other = 'retest other';

SELECT * FROM table_with_column_that_tracks_modification_date; -- observe mod_timestamp has now been updated

[zipperer]

We have set up an example for each type of tracking

• tracking modification_datetime for each row in a table
• tracking INSERT, UPDATE, DELETE on other tables in the logging.t_history table.