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!