Transition Tables

Transition tables expose NEW TABLE and OLD TABLE to statement-level triggers, giving bulk OLD/NEW access without per-row trigger cost.

Recipe

CREATE TRIGGER tr_audit_stmt
AFTER INSERT ON app.accounts
REFERENCING NEW TABLE AS inserted
FOR EACH STATEMENT EXECUTE FUNCTION app.audit_inserted();

When to reach for this: You implement or review SQL using this concept in PostgreSQL 18.

Working Example

CREATE OR REPLACE FUNCTION app.audit_inserted() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO app.audit_log (action, cnt)
  SELECT 'insert', count(*) FROM inserted;
  RETURN NULL;
END;
$$;

What this demonstrates:

• REFERENCING NEW TABLE AS inserted names transition relation
• Statement trigger aggregates inserted rows once
• Useful for audit of COPY and bulk INSERT

Deep Dive

Syntax

• Available on INSERT, UPDATE, DELETE statement triggers.
• OLD TABLE on UPDATE/DELETE; NEW TABLE on INSERT/UPDATE.
• Transition relations are temporary like trigger transition tables in SQL standard.

Gotchas

• Long transactions - Block vacuum and increase bloat.. Fix: Commit quickly; set timeouts.
• Missing indexes - Seq scans under load.. Fix: Index join and filter columns.
• Implicit locks - Surprise blocking.. Fix: Use explicit FOR UPDATE when needed.
• Wrong isolation assumption - Anomalies under concurrent writes.. Fix: Pick level deliberately.
• Untested migrations - DDL lock or invalid objects.. Fix: Stage migrations with production-like data.

Alternatives

FAQs

Related

• Triggers Basics - timing
• Audit Triggers - audit patterns

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.