129

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?

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
bichonfrise74
  • 1,987
  • 4
  • 16
  • 11
  • afaik not as easy in PostgreSQL where you need a trigger: http://www.pointbeing.net/weblog/2008/03/mysql-versus-postgresql-adding-a-last-modified-column-to-a-table.html – mechanical_meat Jun 24 '09 at 00:58
  • 2
    It's worth noting that MySQL has a number of "special treatments" for `timestamp` columns depending on version and settings, which (fortunately!) cannot be reproduced in Postgres. Like allowing `0` for a `timestamp` column or transforming `NULL` into the current timestamp on input in certain constellations. Be sure to study the manual of both RDBMS to be aware of subtle differences: [MySQL](http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html) and [Postgres](http://www.postgresql.org/docs/current/interactive/datatype-datetime.html). – Erwin Brandstetter Oct 24 '14 at 00:57
  • 3
    @ErwinBrandstetter is the answer provided below still the best practice for autoupdating timestamps on 2018? – CommonSenseCode Aug 25 '18 at 16:30

1 Answers1

168

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();
Charles Ma
  • 47,141
  • 22
  • 87
  • 101
  • 5
    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. – bichonfrise74 Jun 24 '09 at 01:10
  • 11
    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. – Charles Ma Jun 24 '09 at 01:38
  • 3
    There is practically no performance penalty - at least in any sensible database. –  Jun 24 '09 at 07:44
  • 1
    You'll initially need to load the language with `CREATE LANGUAGE plpgsql;` That's only needed once. – Daniel Winterstein Nov 14 '11 at 14:08
  • 6
    Is there a way to give the column name that need to be updated as parameter to the function `update_changetimestamp_column`? – Antoan Milkov Oct 02 '14 at 07:14
  • @AntoanMilkov yes, `CREATE TRIGGER` can take arguments (pass them as literals to the function specification). – womble Oct 16 '14 at 01:19
  • 10
    @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. – MirroredFate Feb 23 '16 at 19:37