MySQL ON UPDATE CURRENT_TIMESTAMP → PostgreSQL Migration

Why PostgreSQL has no column-level auto-update-on-modify clause, and how to replace it with a BEFORE UPDATE trigger.

HIGH

Quick answer

PostgreSQL has no column-definition equivalent to ON UPDATE CURRENT_TIMESTAMP. Write a BEFORE UPDATE trigger that sets NEW.updated_at = now() on every row update.

Why it breaks

MySQL lets a TIMESTAMP or DATETIME column auto-refresh itself on every UPDATE purely through column-definition syntax — no trigger required. PostgreSQL has no equivalent clause, so a naive port that just drops "ON UPDATE CURRENT_TIMESTAMP" from the column definition silently loses the auto-update behavior entirely: the column will simply stop changing on UPDATE, with no error to signal the gap.

Real examples

Column definition to trigger

CREATE TABLE posts (
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE posts (
  updated_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TRIGGER posts_set_updated_at
  BEFORE UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

DEFAULT now() replaces the initial-value part; the trigger replaces the on-update part — two separate mechanisms doing what one MySQL clause did.

Reusable trigger function (define once, attach to every table)

-- N/A — MySQL needs no equivalent function; the behavior is built into the column syntax

CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Write this function once per database; every table that previously used ON UPDATE CURRENT_TIMESTAMP just attaches a trigger to it, rather than duplicating the logic.

Safe migration options

Write one trigger function (e.g. set_updated_at()) that sets NEW.<column> := now(), and attach it via a BEFORE UPDATE trigger to every table that needs this behavior — a single reusable function works across all of them.
Use clock_timestamp() instead of now() only if you need the time to change between multiple updates inside the same transaction — now() is fixed for the whole transaction, matching MySQL's typical behavior more closely for most cases.

PostgreSQL solution

CREATE FUNCTION set_updated_at() RETURNS trigger AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ LANGUAGE plpgsql; then CREATE TRIGGER ... BEFORE UPDATE ON table FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Validation

Before cutover

After migration, UPDATE one row on a table that used to have ON UPDATE CURRENT_TIMESTAMP, changing an unrelated column, and confirm the timestamp column actually changed — this is the single most common silently-dropped behavior in this kind of migration, so verify it directly rather than assuming the trigger fired correctly.

Scan your own schema

This page documents detector rule ON_UPDATE_CURRENT_TIMESTAMP in the DevEquiv analyzer — paste or upload your real MySQL/MariaDB schema to find every occurrence of this (and 19 other documented issues) automatically, with a prioritized readiness report.

Analyze a schema free

References

MySQL Manual — Automatic Initialization and Updating for TIMESTAMP and DATETIME (v8.0) — checked 2026-07-30
PostgreSQL Manual — Trigger Behavior (v17) — checked 2026-07-30

Last verified 2026-07-30.