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

- 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?


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
  • deleted_at how would you automated that anyway? What would that even look like? Just set it when you delete the post.
  • Isn't there something like MySQL's ON UPDATE CURRENT_TIMESTAMP? – vinnylinux Jan 19 '18 at 4:35
  • No PostgreSQL tries not to create a complex system of adhoc macros. But the spi method is pretty easy. CREATE TRIGGER mdt_table BEFORE UPDATE ON table FOR EACH ROW EXECUTE PROCEDURE moddatetime (prefix_last_modified); see the link above Just give it the table, and column name. – Evan Carroll Jan 19 '18 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.