insert_username

functions for tracking who changed a table
Repository
https://www.postgresql.org/docs/current/contrib-spi.html#CONTRIB-SPI-INSERT-USERNAME
https://www.postgresql.org/docs/current/contrib-spi.html#CONTRIB-SPI-INSERT-USERNAME

Overview

PackageVersionCategoryLicenseLanguage
insert_username1.0FUNCPostgreSQLC
IDExtensionBinLibLoadCreateTrustRelocSchema
4882insert_usernameNoYesNoYesNoNo-
Relatedpg_idkit pgx_ulid pg_uuidv7 permuteseq pg_hashids sequential_uuids topn quantile

Version

PG18PG17PG16PG15PG14
1.01.01.01.01.0

Install

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

CREATE EXTENSION insert_username;

Usage

insert_username: track who modified a table row

Provides a trigger function that stores the current user’s name into a specified text column.

CREATE EXTENSION insert_username;

Trigger Function

FunctionDescription
insert_username()Store current username in the specified column

Parameter: name of the text column to store the username.

Examples

CREATE TABLE audit_log (
  id serial PRIMARY KEY,
  data text,
  modified_by text
);

-- Track who inserts
CREATE TRIGGER set_insert_user
  BEFORE INSERT ON audit_log
  FOR EACH ROW
  EXECUTE FUNCTION insert_username('modified_by');

-- Track who updates
CREATE TRIGGER set_update_user
  BEFORE UPDATE ON audit_log
  FOR EACH ROW
  EXECUTE FUNCTION insert_username('modified_by');

INSERT INTO audit_log (data) VALUES ('test');
SELECT modified_by FROM audit_log;  -- returns current user

Last Modified 2026-03-12: add pg extension catalog (95749bf)