Track last modification timestamp of a row in Postgres

user_9191907 0
Viewed 13603

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.

timestamp postgresql
2 Answers

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

user_106133360

Expanding on SofienM's brilliant answer (and lolung's comment correction), we can also extend this by automatically updating a column with the user name (role) of the row creator, and updating another column with the user name of the last user to modify an existing row. Below is a complete test you can run (I use two imaginary users (user1 & user2) to demonstrate the test):

--create dummy table, execute as user1
CREATE TABLE public.test_table (
  id SERIAL PRIMARY KEY,
  comments VARCHAR(250),
  create_by VARCHAR(250) DEFAULT user,
  create_dat TIMESTAMPTZ DEFAULT now(),
  modify_by VARCHAR(250) DEFAULT user,
  modify_dat TIMESTAMPTZ DEFAULT NOW()
);

--create function trigger to change a timestamp value upon an update
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.modify_dat = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

--create a trigger to execute the function
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON public.test_table
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();

--test the created by and created date columns, execute this as user1
INSERT INTO public.test_table (comments)
VALUES ('hello world'),
       ('hello system'),
       ('hello universe')
;

--wait a minute or two, execute this query as user1 and observe 'modify_dat' column
UPDATE public.test_table
SET comments = 'hello secret'
WHERE comments = 'hello world';

--create function trigger to change a username value upon an update
CREATE OR REPLACE FUNCTION trigger_set_usertimestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.modify_by = user;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

--create a trigger to execute the function
CREATE TRIGGER set_usertimestamp
BEFORE UPDATE ON public.test_table
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_usertimestamp();

--test the created by and created date columns, execute this as user2 for variation
INSERT INTO public.test_table (comments)
VALUES ('goodbye world'),
       ('goodbye system'),
       ('goodbye universe')
;

--wait a minute or two, execute this query as user2 and observe 'modify_by' column
UPDATE public.test_table
SET comments = 'hello unknown'
WHERE comments = 'hello system';

What would improve this is to make the trigger apply to any/every table in a given schema, not just on one specific table (assuming each table has the 'modify_dat' and 'modify_by' columns of course...).

user_94019900
Related