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
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:
NEWis 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
NEWobject, 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
Step 4: Profit
Now we can insert and update rows in the table, and both the
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