Automatically maintain an updated_at column
A reusable PostgreSQL trigger function that sets updated_at to now() on every UPDATE.
- SQL
- GreenSnippets
- MIT
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END;
$$;
CREATE TRIGGER articles_set_updated_at
BEFORE UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();Example / expected output
-- UPDATE articles SET title = 'New' WHERE id = 1; -- updated_at changes automaticallyHow it works
Relying on application code to set updated_at is easy to forget. One trigger function can be attached to any table with that column. BEFORE UPDATE lets the function change the new row before it is written.
This snippet has been reviewed but was not executed by an automated test. Test it in your own environment before relying on it.
Related snippets
- SQL
Top N rows per group
Get the latest or highest N rows for each category using ROW_NUMBER in a subquery.
- SQL
Soft delete with a partial unique index
Keep deleted rows but still enforce unique emails among active rows using a partial unique index.
- SQL
Walk a tree with a recursive CTE
Query an employee/manager or category hierarchy to any depth using WITH RECURSIVE.
- SQL
Pivot rows into columns with FILTER
Turn row values into columns (for example monthly totals) using conditional aggregates, without crosstab.