Automated Updated of Timestamp fields in Postgres

It is often a good idea to include a column in the database design that stores the timestamp of the most recent update.

However, making sure it's always updated can be tedious. This is where Triggers in Postgres comes in handy.

CREATE OR REPLACE FUNCTION update_timestamp()

RETURNS TRIGGER AS $$

BEGIN

  NEW.timestamp = now(); 

  RETURN NEW;

END;

$$ language 'plpgsql';

This only needs to be created once for the entire database if all tables use the same name for this column as a naming convention. Note that "NEW" is a special context / environment variable created on each call. It holds the "new database row for INSERT/UPDATE operations in row-level triggers".

Then for each table (e.g. book):

CREATE TRIGGER book_timestamp BEFORE INSERT OR UPDATE ON book

FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

References:

• http://www.revsys.com/blog/2006/aug/04/automatically-updating-a-timestamp-column-in-postgresql/
• http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html

Note: Not tried this before but someone mentioned here about how it's done in MySQL: http://stackoverflow.com/questions/1035980/postgresql-update-timestamp-when-row-is-updated