If you have database tables in which you are frequently adding or updating data, then it can be useful to have columns which indicates precisely when a specific record was created and updated.
Creating Timestamp Columns
Ideally you’d want to add these columns when you create the table.
create table if not exists social (
id serial primary key,
twitter varchar(15),
linkedin varchar(29),
created_at timestamptz null default now(),
updated_at timestamptz null default now()
);
If, however, you’ve already created the table (and potentially started populating it)…
create table if not exists social (
id serial primary key,
twitter varchar(15),
linkedin varchar(29)
);
… then you can add these columns later.
alter table social add created_at timestamptz null default now();
alter table social add updated_at timestamptz null default now();
With those columns in place, any new record inserted into the table will have both the created_at
and updated_at
columns set to the precise time at which the record was created. We’re 50% of the way there.
Trigger for Updates
We’ll set up a trigger function which will set the updated_at
field.
create or replace function trigger_set_updated_at()
returns trigger as $$
begin
new.updated_at = now();
return new;
end;
$$ language plpgsql;
Pulling the Trigger
And the final step is to attach the trigger function to the table.
create trigger set_timestamp
before update on social
for each row
execute procedure trigger_set_updated_at();
Testing
The proof is in the pudding, so let’s give this a test. First we’ll insert a record.
insert into social (twitter, linkedin) values ('rbranson', 'rbranson');
select * from social;
id|twitter |linkedin|created_at |updated_at |
--+--------------+--------+-----------------------------+-----------------------------+
1|rbranson |rbranson|2022-02-23 05:39:42.212 +0000|2022-02-23 05:39:42.212 +0000|
Looks good. Note that the created_at
and updated_at
fields hold the same value. Now we’ll correct the twitter
field.
update social set twitter = 'richardbranson' where linkedin = 'rbranson';
select * from social;
id|twitter |linkedin|created_at |updated_at |
--+--------------+--------+-----------------------------+-----------------------------+
1|richardbranson|rbranson|2022-02-23 05:39:42.212 +0000|2022-02-23 05:40:51.353 +0000|
The updated_at
field has been amended to reflect the time at which the record was altered.
SQLAlchemy
If you’re using SQLAlchemy then you can add suitable columns to your class definition.
from sqlalchemy import func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, TIMESTAMP
Base = declarative_base()
class Social(Base):
__tablename__ = "social"
id = Column(Integer, primary_key=True)
twitter = Column(String(15))
linkedin = Column(String(29))
created_at = Column(TIMESTAMP, server_default=func.now())
updated_at = Column(TIMESTAMP, server_default=func.now(), onupdate=func.current_timestamp())
💡 If, instead of TIMESTAMP
, the column is of type Date
then use func.current_date()
to set its initial value.
Conclusion
This simple approach allows you to keep track of when records were created or updated. If you need to be more granular (like knowing which fields was updated and when) then you need to work a bit harder. But this already adds some very useful metadata.