From: | Thomas Kellerer <shammat(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Determining if a table really changed in a trigger |
Date: | 2021-10-27 08:02:27 |
Message-ID: | 021f82ab-110a-a663-1de2-687ef3645780@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jayadevan M | 2021-10-27 10:48:38 | Re: String comparison fails for some cases after migration |
Previous Message | Mitar | 2021-10-27 07:50:15 | Re: Determining if a table really changed in a trigger |