Automatically Set Timestamp Column In PostgreSQL

It's been a long time since I need to create a table by myself and it need to have created_at and updated_at timestamp column.

Ideally, we want this to be handled automatically so we don't need to set it from our service. We can either use the ORM library (please don't) or handle it from the database.

In my case, I don't like to use ORM library so I go with the latter.
In PostgreSQL, we have a function and trigger that we will use to automatically create/update our time column.

For the created_at column, we can use the PostgreSQL default value to create it for us.

CREATE TABLE users
(
    id  INTEGER NOT NULL PRIMARY KEY,
    username    TEXT NOT NULL,
    created_at  TIMESTAMP DEFAULT current_timestamp,
    updated_at  TIMESTAMP
)

The above snippet will create users table that will set current_timestamp to created_at column if it's not set.

So created_at column already handled by using the default value, next is for the updated_at we will use trigger from PostgreSQL.

-- create set_timestamp function that will set updated_at column
CREATE OR REPLACE FUNCTION set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- create trigger for our users table for UPDATE query 
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE PROCEDURE set_timestamp();

The above snippet will create `set_timestamp()` function that set `updated_at` with NOW() and `set_timestamp` trigger that will be running on each UPDATE query.

When we insert user, we can see that the `created_at` is filled automatically thanks to the default value.

And when we update that user, the updated_at column will be updated automatically each time there is an update query.

That's it!