Online

I have been transitioning from a MySQL to a PostgreSQL environment. It has its ups and downs. In MySQL, it's common to define a table with timestamp columns that automatically update to the current timestamp whenever a row is modified. Here's how you might define such a table:

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

However, PostgreSQL does not support the ON UPDATE CURRENT_TIMESTAMP syntax directly!

Triggers

Updating a field automatically can be achieved by creating a trigger to update updated_at field whenever that row is updated. Triggers in PostgreSQL are functions that are automatically executed or fired when certain events occur on a table or view. These events can be inserts, updates, or deletes. Triggers can be used for enforcing complex business rules, auditing, or maintaining data integrity across related tables.

To mimic the MySQL ON UPDATE CURRENT_TIMESTAMP behavior in PostgreSQL, you'll need to create a trigger. Below is 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

Once you have the trigger function in place, the next step is to activate it on the desired table. Here's how to set it up for Users :

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 do it for insert because default value takes care of that), ensuring that the updated_at column always reflects the last modification time.

Online

Add update date field to Postgres

Continue Reading

Add update date field to Postgres

Continue Reading

Triggers

Activate Trigger