From: Tom Lane <tgl@sss.pgh.pa.us>
To: PALAYRET Jacques <jacques.palayret@meteo.fr>
cc: pgsql-general@lists.postgresql.org
Subject: Re: PostgreSQL trigger how to detect a column value explicitely modified
In-reply-to: <616435641.255753519.1762260528389.JavaMail.zimbra@meteo.fr>
References: <616435641.255753519.1762260528389.JavaMail.zimbra@meteo.fr>
Comments: In-reply-to PALAYRET Jacques <jacques.palayret@meteo.fr>
	message dated "Tue, 04 Nov 2025 12:48:48 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <518933.1762268889.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 04 Nov 2025 10:08:09 -0500
Message-ID: <518934.1762268889@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/518934.1762268889%40sss.pgh.pa.us>
Precedence: bulk

PALAYRET Jacques <jacques.palayret@meteo.fr> writes:
> In a trigger body, is there a simple way to know if a column value has b=
een explicitely modified ? =

> Explicitely modified ; in others words, typically indicated in the SET c=
lause 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 =3D 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