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 theNEW
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