Automatically updating a timestamp column in PostgreSQL using Triggers
Keeping a record of when was a table last modified is very crucial and important for us. Unlike MySql, Postgres doesn’t come with inbuilt functionality for this. So to solve this problem we will be using Triggers and Procedures.
Triggers allow us 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 updated_at(column to store the time of recent update)
value before any UPDATE operation. Here’s how you can do this.
Step 1: Create the Table
Let’s start by creating a table so that we can run all of our operations on this table without breaking anything😜😜.
create table user_task
(
created_on timestamp default CURRENT_TIMESTAMP not null,
updated_on timestamp default CURRENT_TIMESTAMP not null,
status varchar not null
)
This will create a table with the name user_task with columns created_on, updated_on, and status. Created_on and Updated_on are by default current time status. Now let’s insert some data into the table.
insert into user_task (status) values('trying'),('successful'),('failed')
Step 2: Create the Function
Next step is creating a Postgres function which PL/pgSQL programming supports.
CREATE FUNCTION update_updated_on_user_task()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_on = now();
RETURN NEW;
END;
$$ language 'plpgsql';
In this block of code, we defined our function with our logic of updating updated_on with the value of now().
- In the function, NEW variable is a Record Object. New contains the data that’s being inserted or updated in the table. This means we can update and read any value of the row before it gets persisted in the Disk.
- NEW.updated_on = now() means update value of updated_on by value of now().
This way we can update any column using triggers as per our requirements.
Note: You can find more information on Postgres Functions here
Step 3: Create the Trigger
CREATE TRIGGER update_user_task_updated_on
BEFORE UPDATE
ON
user_task
FOR EACH ROW
EXECUTE PROCEDURE update_updated_on_user_task();
This trigger will This trigger will execute the update_updated_on_user_task
() function that we defined earlier. It will do so whenever a row is updated in the user_task
table.
Note: You can find more information on Postgres trigger here
Step 4: Automatic PostgreSQL Timestamps
Now updated_on
will be saved correctly whenever we update rows in the table. That's all it takes!