On some of my tables I have some columns which contain a creation timestamp column that is supposed to be set on insert and a last update timestamp that is supposed to be updated on every update. I would like to do this without using a trigger is that possible?
Asked
Active
Viewed 1,076 times
1
-
1Read the comments on the answer here http://stackoverflow.com/questions/1035980/postgresql-update-timestamp-when-row-is-updated Doesn't seam possible. – The Scrum Meister Mar 08 '11 at 08:28
-
2This is why triggers were invented, just use them. – Frank Heikens Mar 08 '11 at 17:38
1 Answers
2
For creation time column you can set default yo NOW, but for last update time I think you will have to use trigger.
Btw. why you don't want triggers?
Adrian Serafin
- 7,665
- 5
- 46
- 67
-
These columns for a lot of tables and they are just here for auditing purposes, I thought my ddl would be much simpler if there was a data type for timestamp which always updated on update. i have triggers right now and they are too much typing to get them setup. – ams Mar 08 '11 at 18:31
-
2@ams (RE: "too much typing"): If you don't mind having the update timestamp field in each table have the exact same name, then you can just create ONE function with RETURNS TRIGGER that updates it and re-use that trigger function for the UPDATE triggers on all tables containing that field. This method has its pros and cons, but if you already have duplicate field names in different tables with different meanings, then it shouldn't be a big deal (I'm looking at you, "id" field in every table). – Matthew Wood Mar 08 '11 at 23:28
-
@Matthew Wood, yes I have a naming convention that my database follows and I make sure that all tables use it, the fields are always going to be called insert_ts and lastupdate_ts. I have two trigger functions which I reuse on all tables. – ams Mar 09 '11 at 16:16