moddatetime

functions for tracking last modification time

https://www.postgresql.org/docs/current/contrib-spi.html#CONTRIB-SPI-MODDATETIME

Overview

Package	Version	Category	License	Language
`moddatetime`	`1.0`	FUNC	PostgreSQL	C

ID	Extension	Bin	Lib	Load	Create	Trust	Reloc	Schema
4883	`moddatetime`	No	Yes	No	Yes	No	No	-

Related	`tcn` `refint` `autoinc` `insert_username` `table_log`

Version

PG18	PG17	PG16	PG15	PG14
1.0	1.0	1.0	1.0	1.0

Install

Note: This is a built-in contrib extension of PostgreSQL

CREATE EXTENSION moddatetime;

Usage

moddatetime: track modification timestamp

Provides a trigger function that stores the current timestamp when a row is modified.

CREATE EXTENSION moddatetime;

Trigger Function

Function	Description
`moddatetime()`	Store current timestamp in the specified column on UPDATE

Parameter: name of the timestamp or timestamp with time zone column to update.

Examples

CREATE TABLE documents (
  id serial PRIMARY KEY,
  content text,
  modified_at timestamp with time zone
);

CREATE TRIGGER set_modified
  BEFORE UPDATE ON documents
  FOR EACH ROW
  EXECUTE FUNCTION moddatetime('modified_at');

UPDATE documents SET content = 'new content' WHERE id = 1;
-- modified_at is automatically set to current timestamp