No Clocks Vault

SQL - PostgreSQL - Auto-Update Time Stamps

1 min read

PostgreSQL - Auto-Update Time Stamps

Code Properties

  • Language: SQL (PostgreSQL)
  • Extensions: moddatetime

Overview

Simple trigger using the moddatetime extension to automatically update timestamp columns when rows are modified.

Code

CREATE EXTENSION IF NOT EXISTS moddatetime SCHEMA extensions;
 
-- assuming table "todos" with timestamp column "updated_at"
-- this trigger sets "updated_at" to current timestamp on every update
CREATE TRIGGER handle_updated_at 
BEFORE UPDATE ON todos
FOR EACH ROW 
EXECUTE PROCEDURE moddatetime(updated_at);

Usage

-- create table with timestamp columns
CREATE TABLE todos (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    completed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- add trigger
CREATE TRIGGER handle_updated_at 
BEFORE UPDATE ON todos
FOR EACH ROW 
EXECUTE PROCEDURE moddatetime(updated_at);
 
-- now updates automatically set updated_at
UPDATE todos SET completed = true WHERE id = 1;

Appendix

Note created on 2024-05-30 and last modified on 2024-12-31.

See Also

(c) No Clocks, LLC | 2024

Graph View