Update timestamp when row is updated in PostgreSQL

PostgresqlTimestamp

Postgresql Problem Overview


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?

Postgresql Solutions


Solution 1 - Postgresql

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();

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionbichonfrise74View Question on Stackoverflow
Solution 1 - PostgresqlCharles MaView Answer on Stackoverflow