Automatic Timestamping in PostgreSQL

UPDATED ON: Apr 29, 2020

Setup the table schema

CREATE TABLE users (
  id         SERIAL      PRIMARY KEY,
  full_name  TEXT        NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The NOW() function returns the start date and time of the current transaction in TIMESTAMPTZ (timestamp with time zone).

Create the trigger function

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
  BEGIN
    --
    -- Set the `updated_at` TIMESTAMPZ column to the start date and time of the
    -- current transaction.
    --
    NEW.updated_at = NOW();
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

Create the trigger

CREATE TRIGGER tr_users_bu BEFORE UPDATE on users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

The trigger is associated with the users table and will execute the update_updated_at_column function before an UPDATE operation is performed on the table.
Note that a trigger is executed in the same transaction as the triggering action.

Links