Automating timestamp fields in PostgreSQL

Question

I'm trying to define the following table in PostgreSQL:

posts
- uuid
- title
- created_at
- updated_at
- deleted_at

The idea is that i never delete anything, so i change the deleted_at instead of removing the row and use where deleted_at is null for normal behavior.

Is there a way of automating those timestamps?

Answer 1

Different columns,

• created_at this simple, just give it a DEFAULT now()
• updated_at use the spi module which provides a higher-order trigger than can be applied to any table, see this post, also see row-modification-time
• deleted_at how would you automated that anyway? What would that even look like? Just set it when you delete the post.