Alex Rivera | Logout

Postgresql Current timestamp on Update

Asked 2010-03-02T11:54:17.520
24

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

1 Answer

5

Update it with a trigger. Documentation and examples.

answered 2010-03-02T11:56:43.217

Your Answer