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:
NEWis 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
NEWobject 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
Step 4: Automatic PostgreSQL Timestamps
Now both the
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