On update timestamps, MySQL vs Postgres
| CJ Estel
More on my transition from using MySQL to Postgres. I ran into an issue the first time I tried to have a field update the timestamp when the row is changed. In MySQL, this is pretty straight forward. Below is how having an updated_at field is handled in MySQL, and the Postgres equivalent.
MySQL
MySQL has an “ON UPDATE CURRENT_TIMESTAMP” that can be applied to a timestamp field. It restricts this type to a single field on a table (prior to MyQSL 5.6.5), but it can be used in the create table statement or you can alter a field to use it post create. As of MySQL 5.6.5, you can have more than one timestamp field type, and can reference “CURRENT_TIMESTAMP” more than once.
CREATE TABLE t1 (
id INT( 10 ) NOT NULL,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Postgres
Postgres does not have an equivalent to the “on update”, so you must create a trigger to handle it. The default current_timestamp can still be set based on the table creation.
CREATE TABLE t1 (
id INT NOT NULL,
updated_at TIMESTAMP DEFAULT 'now'::timestamp
);
#provided the field is named the same thing in all tables that use this, you can use a centralized function
CREATE FUNCTION update_updated_at_column() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$;
CREATE TRIGGER t1_updated_at_modtime BEFORE UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
