Automatic timestamps with PostgreSQL

Many applications require keeping track of when database records are created and updated.

In PostgreSQL, we can track creation date by adding a created_at column with a default value of NOW(). However, for tracking updates, we will need to make use of triggers.

Triggers allow us to define functions to be executed whenever a certain type of operation is performed. In this case, we will need to make sure that before any UPDATE operation, PostgreSQL runs a function that automatically sets an updated_at value.

Step 1: Create the function

We can create a function using the PL/pgSQL programming language, which is supported by default in PostgreSQL.

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

Since we defined our function with a return type of trigger (RETURNS TRIGGER), PostgreSQL makes some special variables available to us:

• NEW is a RECORD object. It contains the data being inserted or updated. As you can see in the example function, PostgreSQL allows us to read from and write to any field in the NEW object, before it gets saved to disk.

Note: You can find more info on trigger variables here.

Step 2: Create the table

Triggers must be attached to tables, so let us first create a simple table:

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

Step 3: Create the trigger

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

NB: The trigger will execute the trigger_set_timestamp function we defined earlier whenever a row is updated in the todos table.

Step 4: Profit

Now we can insert and update rows in the table, and both the created_at and updated_at columns will be saved correctly.

INSERT INTO todos (content) 
VALUES ('buy milk') RETURNING *;

-[ RECORD 1 ]+------------------------------
id           | 1
content      | buy milk
created_at   | 2017-05-25 17:39:15.014961-05
updated_at   | 2017-05-25 17:39:15.014961-05
completed_at |

INSERT INTO todos (content) 
VALUES ('take out the trash') RETURNING *;

-[ RECORD 1 ]+------------------------------
id           | 2
content      | take out the trash
created_at   | 2017-05-25 17:39:49.554808-05
updated_at   | 2017-05-25 17:39:49.554808-05
completed_at |

UPDATE todos 
   SET completed_at = NOW() 
 WHERE content = 'buy milk' RETURNING *;

-[ RECORD 1 ]+------------------------------
id           | 1
content      | buy milk
created_at   | 2017-05-25 17:39:15.014961-05
updated_at   | 2017-05-25 17:40:28.363034-05
completed_at | 2017-05-25 17:40:28.363034-05