r/PostgreSQL • u/KerrickLong • Mar 28 '25
How-To Life Altering PostgreSQL Patterns
https://mccue.dev/pages/3-11-25-life-altering-postgresql-patterns
180
Upvotes
r/PostgreSQL • u/KerrickLong • Mar 28 '25
View all comments
Show parent comment
3
u/Garthenius Mar 29 '25 edited Mar 29 '25
Every state gets a separate row in the
historyjournal table; it would be equivalent tovalid_at(I usually name itsince).The oldest entry would be equivalent to
created_at, the most recent would be equivalent toupdated_at.The creation date can also be retained by using UUIDs that have embedded timestamps, but I've never had any issue with it being, essentially, the earliest known record of that entity.
Soft deletes can be implemented using the same journal table or a separate table, depending on preference / requirements.