Postgresql Current timestamp on Update

mysql, postgresql

Solution

Just make sure all tables have the same columnname:

CREATE OR REPLACE FUNCTION upd_timestamp() RETURNS TRIGGER 
LANGUAGE plpgsql
AS
$$
BEGIN
    NEW.modified = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

CREATE TRIGGER t_name
  BEFORE UPDATE
  ON tablename
  FOR EACH ROW
  EXECUTE PROCEDURE upd_timestamp();

Problem

What is the postgres equivalent of the below mysql code ``` CREATE TABLE t1 ( created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); CREATE TABLE t2 ( created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ``` As per Alex Brasetvik answer below, it seems i should go with triggers, my problem is i have a number of tables t1, t2... with created and modified fields, is it possible to write a generalized procedure? --update Almost ready ``` CREATE FUNCTION update_timestamp() RETURNS trigger AS $update_timestamp$ BEGIN NEW.modified := current_timestamp; RETURN NEW; END; $update_timestamp$ LANGUAGE plpgsql; CREATE TRIGGER update_timestamp BEFORE INSERT OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE update_timestamp(); CREATE TRIGGER update_timestamp BEFORE INSERT OR UPDATE ON t2 FOR EACH ROW EXECUTE PROCEDURE update_timestamp(); ```

Original source