Lorixen.
EN

Keep created_at and updated_at honest in PostgreSQL

Short answer: Let the database own your timestamps. Give created_at and updated_at a default of now() and make them NOT NULL, then add a BEFORE UPDATE row trigger that sets NEW.updated_at on every real change. That way rows touched by a migration script, a bulk import, a psql session or a second app get the same treatment as rows saved through your framework. Skip no-op updates with a WHEN clause, and backfill old blanks once before you rely on the columns.

Why application-only timestamps drift

Most frameworks fill created_at and updated_at for you, but only when the change goes through the framework's own model layer. Plenty of writes don't:

  • a one-off SQL fix run by hand in production
  • a bulk UPDATE issued through a query builder that bypasses model events
  • a nightly import job written in another language
  • a reporting tool or second service with write access
  • a data migration that copies rows between tables

Each of these can leave updated_at untouched, set to NULL, or set from a server whose clock or time zone differs from the app's. Nobody notices until something depends on the column: an incremental sync that asks for "rows changed since 09:00" misses records, a cache keyed on updated_at serves stale prices, or a support agent can't tell when an order address was edited.

The fix is to move the rule to the one place every write passes through.

Step 1: defaults and NOT NULL

PostgreSQL's documentation describes a timestamp column defaulting to CURRENT_TIMESTAMP as the common way to record the time of row insertion. Use timestamptz so values are stored as absolute moments rather than wall-clock times without a zone:

ALTER TABLE orders
  ALTER COLUMN created_at SET DEFAULT now(),
  ALTER COLUMN updated_at SET DEFAULT now();

Defaults only fill a column when the insert doesn't supply a value. If your code explicitly sends NULL, the default won't help, which is why the next step matters:

ALTER TABLE orders
  ALTER COLUMN created_at SET NOT NULL,
  ALTER COLUMN updated_at SET NOT NULL;

That statement fails if existing rows already contain blanks, so backfill first (step 4).

Step 2: a reusable trigger function

Write one function and attach it to every table that has an updated_at column:

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

In a row-level BEFORE trigger, the row you return is the row that gets written. Changing NEW.updated_at and returning NEW is enough; you never issue a second UPDATE, so there is no loop.

Step 3: attach it, and ignore no-op saves

CREATE TRIGGER orders_set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION set_updated_at();

The WHEN condition comes straight from PostgreSQL's own CREATE TRIGGER examples. It means a save that changes nothing, such as a form resubmitted with identical values, won't bump the timestamp. Incremental syncs then skip unchanged rows. If you'd rather record every touch, drop the WHEN line.

You may also want to stop callers from rewriting history. A second guard in the same function keeps created_at fixed on updates:

NEW.created_at := OLD.created_at;

Put it before RETURN NEW. Any accidental overwrite of the creation time is then quietly reversed.

If a table has more than one BEFORE UPDATE trigger, PostgreSQL fires them in alphabetical order by name. Name the timestamp trigger so it runs last (for example prefix it with zz_) if another trigger modifies the row and you want those changes to count.

Step 4: backfill old rows once

Before adding NOT NULL, fix the gaps with the best evidence you have:

UPDATE orders
SET updated_at = coalesce(updated_at, created_at),
    created_at = coalesce(created_at, updated_at)
WHERE updated_at IS NULL OR created_at IS NULL;

Rows with neither value need a decision. A related table, such as the first payment or the earliest log entry, may give a reasonable creation time; otherwise pick a fixed, clearly documented placeholder date so reports can exclude them. Run large backfills in batches.

Note that the trigger will fire during this backfill and set updated_at to the current time on every row it changes. If you want to keep historical values, create the trigger after the backfill.

now() versus clock_timestamp()

now() and CURRENT_TIMESTAMP return the start time of the current transaction, and they don't change while the transaction runs. PostgreSQL describes this as a feature: everything changed in one transaction shares one consistent "current" time. For audit-style columns that's usually what you want, because an order and its line items saved together get identical timestamps.

clock_timestamp() returns the actual current time and changes even within a single statement. Use it only if you need to distinguish events inside one long transaction, such as a slow batch job, and be aware that rows from one logical change will then carry slightly different times.

Keep the app and the database in agreement

Your framework may still send its own updated_at. The trigger overwrites it, but that can confuse debugging. Two tidy options:

  • turn off the framework's automatic timestamp handling for updated_at and read the value back after saving, or
  • leave the framework on and treat the database value as the authority.

Either way, make sure the app reads the stored value after a save if it shows "last edited" to users or uses the column for optimistic locking.

Checking that it works

A quick test in a transaction you roll back:

BEGIN;
UPDATE orders SET status = status WHERE id = 42;
SELECT updated_at FROM orders WHERE id = 42;
UPDATE orders SET status = 'shipped' WHERE id = 42;
SELECT updated_at FROM orders WHERE id = 42;
ROLLBACK;

With the WHEN clause, the first update leaves the timestamp alone and the second sets it. Add a similar check to your migration tests so a future schema change doesn't silently drop the trigger.

A worked example

An illustrative Leeds catering-supplies wholesaler synced customer accounts from its web shop to a delivery app every fifteen minutes, pulling rows where updated_at was newer than the last run. Drivers sometimes arrived at old addresses. The cause was an admin script that corrected addresses in bulk with plain SQL, leaving updated_at unchanged, so the sync never saw those edits. The developer backfilled blanks, added defaults, NOT NULL and the trigger with a no-op filter, and from then on every address change, whatever its source, appeared in the next sync.

FAQ

Do I need one trigger function per table?

No. One generic function works for every table with an updated_at column; create one trigger per table that calls it.

Will the trigger slow down writes?

It adds a small amount of work per updated row. For typical business apps that's rarely noticeable, but measure on your largest bulk jobs.

Should updated_at change when a child row changes?

Only if your app treats the parent as changed. If so, touch the parent explicitly in the same transaction or add a trigger on the child table.

If your syncs, caches or reports keep disagreeing about when records changed, tell us how your data flows and we'll help you find where the timestamps slip.

Sources

WhatsAppBook a call