Subject: PostgreSQL trigger how to detect a column value explicitely modified

From: PALAYRET Jacques <[email-masked]>
Date: Tue, 4 Nov 2025 12:48:48 +0000 (GMT)

Hello, 

In a trigger body, is there a simple way to know if a column value has been explicitely modified ? 
Explicitely modified ; in others words, typically indicated in the SET clause of the UPDATE. 
A simple way ; I mean without analysing the SQL statement which can be very difficult according to the statement. 

# My trigger function executed by trigger BEFORE UPDATE ON a table tb (with columns id, c2, c3 ; of integer type) FOR EACH ROW : 
CREATE OR REPLACE FUNCTION func_tg_upd() 
RETURNS trigger LANGUAGE plpgsql 
AS $function$ 
BEGIN 
if NEW.c2 != 1 
then 
NEW.c2 := 0 ; 
end if; 
return NEW; 
END; 
$function$ 
; 

# Actions of the trigger 
UPDATE tb 
SET c 2 =2 
WHERE ... -- old c2 value can be 2 or another value 
; 
=> c2 will be changed to 0 by the trigger, OK , because I explicitely indicated the new value of c2 to a value different from 1. 
The the old value of c2 could be different or not. 
Example : 
TABLE tb ; 
id | c2 | c3 
-----+----+---- 
100 | 1 | 2 

UPDATE tb 
SET c2=2 
; 
TABLE tb ; 
id | c2 | c3 
-----+----+---- 
100 | 0 | 2 

---------- ---------- ---------- ---------- ---------- 

UPDATE tb 
SET c 3 =3 
WHERE ... -- and c2=2 ( previous value of c2 is 2 ) 
; 
=> c2 will be changed to 0 by the trigger, but I don't want that action ( NOT OK for me ) because I didn't explicitely indicate the new value 2 to c2 . 
Example : 
TABLE tb ; 
id | c2 | c3 
-----+----+---- 
100 | 2 | 2 

UPDATE tb 
SET c3=3 
; 
TABLE tb ; 
id | c2 | c3 
-----+----+---- 
100 | 0 | 3 

Regards 
----- Météo-France ----- 
PALAYRET Jacques 
DCSC/GDC 
[email-masked] 
Fixe : +33 561078319

---

From: Dominique Devienne <[email-masked]>
Date: Tue, 4 Nov 2025 16:00:42 +0100

> In a trigger body, is there a simple way to know if a column value has been explicitely modified ?

Using pg_trigger_depth(), you can know whether the trigger is called
from "outer SQL" directly,
or from SQL done within another trigger (because the depth will be
larger). I didn't quite follow
your description, to be honest, but I suspect the above is what you
want (maybe :)). --DD

PS: To illustrate, we have this trigger to enforce some of our tables
are "trigger managed",
and no DMLs should be done "directly" on them (only from triggers). FWIW. --DD

PPS: pg_trigger_depth() is 0 if the trigger function is called
directly (unusual).
  1 if directly called from an "outer SQL" statement (from a proc/func or not).
  2 or more if triggered from SQL done by another (possibly the same)
"triggered" trigger.

CREATE FUNCTION trigger_managed_tf()
RETURNS TRIGGER
AS $$
BEGIN
    IF pg_trigger_depth() < 2 THEN
        RAISE EXCEPTION 'Direct insert/update/delete are not allowed
on the % table.', TG_TABLE_NAME;
    END IF;
    RETURN COALESCE (NEW, OLD);
END
$$ LANGUAGE plpgsql

---

From: Tom Lane <[email-masked]>
Date: Tue, 04 Nov 2025 10:08:09 -0500

> Explicitely modified ; in others words, typically indicated in the SET clause of the UPDATE. 

I believe that an ON UPDATE trigger coded in C can access a bitmapset
that shows which column(s) are targeted in the SET clause; but we've
not exposed that to PL/pgSQL or other higher-level languages.

There are of course a bunch of definitional issues.  Should
"UPDATE ... SET x = x" count as an update?  What if some earlier
(... or later ...) BEFORE trigger changes a column?  We don't
provide any help for those cases either.

I think most people settle for testing "OLD.col IS DISTINCT FROM
NEW.col", which you could argue is a good operational definition
of whether the column changed.

			regards, tom lane