in Databases

On update timestamps, MySQL vs Postgres

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

Write a Comment

Comment

    • “now()” doesn’t use the timestamp when the table is created, it uses the time when a record is being inserted