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