Update timestamp when row is updated in PostgreSQL

Question

In MySQL, we can execute this where it updates the column changetimestamp every time the row is changed:

create table ab (
  id int, 
  changetimestamp timestamp 
    NOT NULL 
    default CURRENT_TIMESTAMP 
    on update CURRENT_TIMESTAMP 
);

Is there something similar to do the above in PostgreSQL?

afaik not as easy in PostgreSQL where you need a trigger: pointbeing.net/weblog/2008/03/… — mechanical_meat
– mechanical_meat, Commented Jun 24, 2009 at 0:58
@ErwinBrandstetter is the answer provided below still the best practice for autoupdating timestamps on 2018? — CommonSenseCode
– CommonSenseCode, Commented Aug 25, 2018 at 16:30

Charles Ma · Accepted Answer · 2009-06-24 01:06:31Z

190

Create a function that updates the changetimestamp column of a table like so:

CREATE OR REPLACE FUNCTION update_changetimestamp_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.changetimestamp = now(); 
   RETURN NEW;
END;
$$ language 'plpgsql';

Create a trigger on the table that calls the update_changetimestamp_column() function whenever an update occurs like so:

    CREATE TRIGGER update_ab_changetimestamp BEFORE UPDATE
    ON ab FOR EACH ROW EXECUTE PROCEDURE 
    update_changetimestamp_column();

answered Jun 24, 2009 at 1:06

Charles Ma

49.6k22 gold badges92 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

bichonfrise74 Over a year ago

So, there is no other way of doing what I want except through a trigger? Because I would like to implement 'on update timestamp' for all of my tables possibly 300+. And I'm thinking that creating triggers might cause some performance issues.

Charles Ma Over a year ago

As far as I know this is the standard way to do this in postgresql. When you write "on update current_timestamp" in mysql, it creates a trigger on the table in the background. The difference is that you're writing the trigger here manually instead of having it written for you.

user80168 Over a year ago

There is practically no performance penalty - at least in any sensible database.

Antoan Milkov Over a year ago

Is there a way to give the column name that need to be updated as parameter to the function update_changetimestamp_column?

MirroredFate Over a year ago

@womble It would likely be very useful to post an example of this. If I manage how to dynamically specify which column to update, I'll write it as an answer.

|

Collectives™ on Stack Overflow

Update timestamp when row is updated in PostgreSQL

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related