Postgresql Current timestamp on Update

Question

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

Frank Heikens · Accepted Answer · 2010-03-02 12:31:05Z

32

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

answered Mar 2, 2010 at 12:31

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

Comments

Lauri Silvennoinen · Accepted Answer · 2012-04-20 12:36:54Z

13

Thank you for the information Mithun and Alex Brasetvik.

I'd like to add one minor tweak to the trigger. Since we mostly likely want the modified column to store the timestamp when the row was last changed, not when it was the target of an UPDATE statement, we have to compare the new and the old value of the row. We update the modified column only if these two values differ.

CREATE OR REPLACE FUNCTION update_modified_timestamp() RETURNS TRIGGER 
LANGUAGE plpgsql
AS
$$
BEGIN
    IF (NEW != OLD) THEN
        NEW.modified = CURRENT_TIMESTAMP;
        RETURN NEW;
    END IF;
    RETURN OLD;
END;
$$;

This trigger ensures that the modified column is updated only if the UPDATE operation actually changes the values stored in the row.

answered Apr 20, 2012 at 12:36

Lauri Silvennoinen

1311 silver badge2 bronze badges

1 Comment

Federico Baù Sep 25 at 4:45

I think makes sense, but I run the query to ChatGPT and found few issues. this is the result --> stackoverflow.com/a/79774370/13903942

Alex Brasetvik · Accepted Answer · 2010-03-02 11:56:43Z

5

Update it with a trigger. Documentation and examples.

answered Mar 2, 2010 at 11:56

Alex Brasetvik

11.8k2 gold badges41 silver badges38 bronze badges

Comments

Bungeefan · Accepted Answer · 2022-06-07 19:21:57Z

3

To further improve, the answer given by @Lauri Silvennoinen:

This trigger uses the WHEN clause as recommended by the official docs to check for changes in the row even before calling the specified function.

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

CREATE OR REPLACE TRIGGER tg_update_modified
    AFTER UPDATE ON table_name
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE FUNCTION update_modified_timestamp();

answered Jun 7, 2022 at 19:21

Bungeefan

1583 silver badges9 bronze badges

Comments

Federico Baù · Accepted Answer · 2025-09-25 04:44:38Z

Improvement from Lauri Silvennoinen https://stackoverflow.com/a/10246381/13903942

I've asked ChatGPT to check for issue from the suggest query, these are the results:

CREATE OR REPLACE FUNCTION util.touch_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW IS DISTINCT FROM OLD THEN
        NEW.updated_at := CURRENT_TIMESTAMP;
    END IF;
    RETURN NEW;
END;
$$;

Add to a table

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON auth.users
FOR EACH ROW
EXECUTE FUNCTION util.touch_updated_at();

Why?

🔹 Issues with IF (NEW != OLD)

In PL/pgSQL, != between two records (NEW and OLD) doesn’t work the way you expect.
You’ll get an error like:
```
ERROR:  operator does not exist: record <> record
```
PostgreSQL doesn’t define a direct row-to-row comparison operator. You need to use IS DISTINCT FROM

🔹 The proper check

IF NEW IS DISTINCT FROM OLD THEN
    NEW.modified := CURRENT_TIMESTAMP;
    RETURN NEW;
END IF;
RETURN OLD;🔹 The proper check
IF NEW IS DISTINCT FROM OLD THEN
    NEW.modified := CURRENT_TIMESTAMP;
    RETURN NEW;
END IF;
RETURN OLD;

IS DISTINCT FROM is null-safe (unlike <>).
→ So if one column is NULL and the other isn’t, it’s considered different.
→ If both are NULL, they’re considered the same.

Collectives™ on Stack Overflow

Postgresql Current timestamp on Update

5 Answers 5

Comments

1 Comment

Comments

Comments

I've asked ChatGPT to check for issue from the suggest query, these are the results:

Why?

🔹 The proper check

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

I've asked ChatGPT to check for issue from the suggest query, these are the results:

Why?

🔹 The proper check

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related