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