Simple Insert and Update Logging in PostgreSQL

Being able to see at what time a row was inserted or updated in PostgreSQL is easy and can be put to good use.

In Using Base Tables in Postgres I showed how to create tables with inserted and updated times. Until now though, the updated column was exactly the same as inserted, but let's change that. Just to review our base table:

 CREATE TABLE base (
    inserted        TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated         TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
 );

Looking at this, both inserted and updated default to whatever the time is at insert. What we want though is to change the updated column whenever the row changes (ie. it is updated). To do this we need to set a trigger on the table which calls a function when each row is updated.

Diving right in, here's the function we need:

 CREATE FUNCTION updated() RETURNS trigger as '
    BEGIN
       NEW.updated := CURRENT_TIMESTAMP;
       RETURN NEW;
    END;
 ' LANGUAGE plpgsql;

It's a fairly simple function and basically says to set the updated column of the NEW (or in this case, the updated) row, and return it ready to be put into the table. Further information says that the return type is trigger (makes sense) and it is written in the PL/pgSQL language.

At the moment though, this function is standalone and isn't being called from anywhere. So let's create a simple table and set an updated trigger on it:

 CREATE TABLE news (
    id              SERIAL,
    title           TEXT NOT NULL,
    article         TEXT NOT NULL,
    LIKE base INCLUDING DEFAULTS
 );
 
 CREATE TRIGGER news_updated BEFORE UPDATE ON news
     FOR EACH ROW EXECUTE PROCEDURE updated();

We have told Postgres is to run this trigger for every single row that is ever updated in the news table. It runs just before the actual update therefore we can change the values of any fields in the NEW row inside the function which are then reflected in the table afterwards. In our case, the updated row will be changed to be the CURRENT_TIMESTAMP, which is what we wanted.

Try inserting a few news articles and see that for every row you added, the inserted and the updated fields are exactly the same:

 INSERT INTO news(title, article) VALUES('Aliens Invade!', '...stub...');
 INSERT INTO news(title, article) VALUES('Earth Loses!', '...stub...');
 SELECT title, inserted, updated FROM news;

Now update the second news item:

 UPDATE news SET title = 'Earth Wins!' WHERE  title = 'Earth Loses!';
 SELECT title, inserted, updated FROM news;

And we're done. This technique is certainly useful in some circumstances to help you track down what's going on in various parts of your database. Of course there are many more and comprehensive techniques for transaction logging but this is a simple one which might save your bacon one day.

This post originated on http://chilts.org/.

Email me on andychilton -at- gmail -dot- com.

Published

13 August 2006

Simple Insert and Update Logging in PostgreSQL

Published

Tags