Geek asks and answers

PostgreSQL: how to get the previous insert timestamp

Suppose I have a table

create table foo (
    insert_current timestamp default now(),
    insert_previous timestamp,
    bar int primary key,
    baz varchar(10)
);

The data will be replaced now, and then when a new batch arrives. I would like to track when the previous batch was inserted as well as the current timestamp. What would be a good way to do this?

+3
source share
2 answers

I would create a trigger that automatically sets insert_current and insert_previous in UPDATE statements.

First create a trigger function:

CREATE OR REPLACE FUNCTION do_update() RETURNS "trigger"
AS $$
BEGIN
    NEW.insert_previous := OLD.insert_current;
    NEW.insert_current := NOW();
    return NEW;
END;
$$
LANGUAGE plpgsql;

Then add the trigger to the table:

CREATE TRIGGER do_update
BEFORE UPDATE ON foo
FOR EACH ROW
EXECUTE PROCEDURE do_update();
+3
source

When you update the line, copy the value insert_currentto insert_previousand set insert_currentto the current timestamp:

UPDATE foo
   SET baz = 'whatever',
       insert_previous = insert_current,
       insert_current  = NOW()
 WHERE bar = 1;
+3
source

More articles:


All Articles