Re: Determining if a table really changed in a trigger

> On 26 Oct 2021, at 16:16, Marcos Pegoraro <marcos@f10.com.br> wrote:
>
>
>> Don’t use this approach with JSON (as opposed to JSONB) type fields though, a single extra space in the JSON
structurewould 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
thatin 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
NEWfields 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.