Track last modification timestamp of a row in Postgres

Question

In Postgres I want to store table's last update/insert time. Microsoft SQL Server offers a type timestamp which is automatically maintained by the database.

But timestamp in Postgres works differently, it is not updated automatically and the column is always null.

Answer 1

In postgresql, you have to use a trigger. You can follow this link on how to do it https://x-team.com/blog/automatic-timestamps-with-postgresql/ .

To summarize the article you can do the following:

1. Create the Pl/Pgsql function that will be triggered:

   CREATE OR REPLACE FUNCTION trigger_set_timestamp()
   RETURNS TRIGGER AS $$
   BEGIN
     NEW.updated_at = NOW();
     RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   

2. Create your table

   CREATE TABLE mytable (
     id SERIAL NOT NULL PRIMARY KEY,
     content TEXT,
     updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
   );
   

3. And finally add the trigger:

   CREATE TRIGGER set_timestamp
   BEFORE UPDATE ON mytable
   FOR EACH ROW
   EXECUTE PROCEDURE trigger_set_timestamp();
   

You can find more informations about the question here: https://dba.stackexchange.com/questions/58214/getting-last-modification-date-of-a-postgresql-database-table

Hope it'll help you.