Re: Determining if a table really changed in a trigger

[Thomas Kellerer <shammat@xxxxxxx>]

Mitar schrieb am 26.10.2021 um 09:05:
> I would like to test inside trigger_function if the table really
> changed. I have tried to do:
>
> PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL
> (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1;
> IF FOUND THEN
>   ... changed ...
> END IF;
>
> But this fails if the table contains a JSON field with the error:
>
> could not identify an equality operator for type json
>

If you use jsonb (which is recommended over json anyway), then this
would work.

I would probably use a row level trigger instead of a statement level trigger

Then compare the two records using IS DISTINCT FROM


   if new is distinct from old then
      ....
   end if;

> I want to skip trivial updates (those which have not changed anything).

It seems you might want to use the pre-defined function suppress_redundant_updates_trigger()

https://www.postgresql.org/docs/current/functions-trigger.html

Might be faster than a self written trigger.

Regards
Thomas