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.
![](https://sleey.dev/content/images/2023/12/image-4.png)
And when we update that user, the updated_at column will be updated automatically each time there is an update query.
![](https://sleey.dev/content/images/2023/12/image-5.png)
That's it!