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

Continue Reading