Re: Determining if a table really changed in a trigger

> On 26 Oct 2021, at 16:16, Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> wrote:
>
>
>> Don’t use this approach with JSON (as opposed to JSONB) type fields though, a single extra space in the JSON structure would already lead to a difference, as would other formatting differences.
>>
> I don´t think two equal values being converted to json will be different in any way. If row_to_json of both are different, I suppose both record really are different, no ?

For row_to_json, as it’s the system that combines the fields in a row into a JSON structure and it probably would do that in the same way each time.

The OP however has a field of type JSON in their table, and that can contain the same information between the OLD and NEW fields formatted in a slightly different way.

For example:

=> with x as (
select '{ "x": 1, "y": 2 }'::json
union all
select '{ "y": 2, "x": 1 }'::json
)
select row(x.json)::text, md5(row(x.json)::text) from x;
row | md5
----------------------------+----------------------------------
("{ ""x"": 1, ""y"": 2 }") | 84df40e8660dcf371d89dbf5d6a61c3d
("{ ""y"": 2, ""x"": 1 }") | abd6db88c2526be6ea97570aeec7e020
(2 rows)

Whereas:

=> with x as (
select '{ "x": 1, "y": 2 }'::jsonb
union all
select '{ "y": 2, "x": 1 }'::jsonb
)
select row(x.jsonb)::text, md5(row(x.jsonb)::text) from x;
row | md5
--------------------------+----------------------------------
("{""x"": 1, ""y"": 2}") | d5a6dbdec7a5bfe0dc99e090db30322e
("{""x"": 1, ""y"": 2}") | d5a6dbdec7a5bfe0dc99e090db30322e
(2 rows)

Alban Hertroys
--
There is always an exception to always.