Add update date field to Postgres

Transitioning from MySQL to PostgreSQL can be a bit of a rollercoaster. One of the quirks you'll encounter is how each handles automatic timestamp updates. In MySQL, you can easily set up a table with timestamp columns that auto-update whenever a row changes. Here's a typical MySQL setup:

CREATE TABLE Users (
	...
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

But PostgreSQL doesn't support the ON UPDATE CURRENT_TIMESTAMP syntax directly. So, how do you achieve the same functionality?

Triggers

In PostgreSQL, you can use triggers to automatically update a field. Triggers are functions that execute automatically when certain events occur on a table, like inserts, updates, or deletes. They're great for enforcing business rules, auditing, or maintaining data integrity.

To mimic MySQL's ON UPDATE CURRENT_TIMESTAMP behavior, you'll need to create a trigger. Here's a trigger function that updates the updated_at field to the current timestamp whenever a row is updated:

CREATE OR REPLACE FUNCTION trigger_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Activate Trigger

After creating the trigger function, you need to activate it on your table. Here's how to set it up for the Users table:

CREATE TRIGGER update_users_updated_on
    BEFORE UPDATE
    ON
        Users
    FOR EACH ROW
EXECUTE PROCEDURE trigger_updated_at();

This setup ensures that the updated_at column is automatically updated to the current timestamp every time a row is modified. You don't need to worry about inserts because the default value handles that.

tl;dr

To auto-update a timestamp in PostgreSQL like MySQL's ON UPDATE CURRENT_TIMESTAMP, use a trigger. Create a trigger function to set updated_at to NOW() on updates, then activate it on your table with a BEFORE UPDATE trigger.

Continue Reading