How to Create Automatic PostgreSQL Timestamps

Many applications require database timestamps whenever a database record is created or updated. In PostgreSQL, you can track the creation date of a database record by adding a created_at column with a default value of NOW(). But for tracking updates, you need to make use of Postgres triggers.

Triggers allow you to define functions that execute whenever a certain type of operation is performed. In this case, to update a Postgres timestamp, you need to run a function that automatically sets an updated_at value before any UPDATE operation. Here's how you can do this.

Step 1: Create the Function

Let's start by creating a Postgres function with 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;

In this block of code, we defined our function with a RETURNS TRIGGER. This opens up a special variable for us to use:

• NEW is a RECORD object. It contains the data that's 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 information on Postgres trigger variables here.

Step 2: Create the Table

Triggers must be attached to tables, so let's 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();

This trigger will execute the trigger_set_timestamp function that we defined earlier. It will do so whenever a row is updated in the todos table.

Step 4: Automatic PostgreSQL Timestamps

Now both the created_at and updated_at columns will be saved correctly whenever we insert and update rows in the table. That's all it takes!

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