How to "succinctly" detect changed values in the PostgreSQL's upsert (on conflict) where clause while supporting null changes?

We maintain a large data warehouse implemented in PostgreSQL and python. One very common pattern we do is to make upserts, and log when things were updated. We have some unique key my_key and values, say, my_uuid, my_text, my_int, my_date. If any any of those values change for a given my_key we would like to update the row. That's fine, and we have a pattern which works well:

insert into my_table (
    my_key,
    my_uuid,
    my_text,
    my_int,
    my_date
)
select
    some_key,
    some_uuid,
    some_text,
    some_int,
    some_date
from some_table
on conflict (my_key) do update set
    some_uuid = excluded.some_uuid,
    some_text = excluded.some_text,
    some_int = excluded.some_int,
    some_date = excluded.some_date,
    update_timestamp = now()
where 
    coalesce(my_table.some_uuid, uuid_nil()) <> coalesce(excluded.some_uuid, uuid_nil())
    or coalesce(my_table.some_text, '') <> coalesce(excluded.some_text, '')
    or coalesce(my_table.some_int, -1) <> coalesce(excluded.some_int, -1)
    or coalesce(my_table.some_date, '3000-01-01'::date) <> coalesce(excluded.some_date, '3000-01-01'::date)

The last on conflict ... where clause is important, because it makes sure that the update_timestamp is only updated when there are changes. It also makes sure we don't update rows unnecessarily, improving performance.

Anyway, we often have an issue with the coalesce() logic. The reason it exists in this pattern is to support the value going to and from null. Let's take the following example:

coalesce(my_table.some_text, '') <> coalesce(excluded.some_text, '')

This works fine, and produces the following results for a comprehensive list of test cases:

```
select coalesce('a', '') <> coalesce('a', '') --> false
union all
select coalesce(null, '') <> coalesce(null, '') --> false
union all
select coalesce('a', '') <> coalesce('b', '') --> true
union all
select coalesce(null, '') <> coalesce('b', '') --> true
union all
select coalesc

You can use is distinct from as gsiems mentioned which is the null safe "not equals" operator. null is distinct from null is false, and 42 is distinct from null is true.

Your testbed:

select
    v1, v2, expected,
    v1 is distinct from v2 as is_different
from (
  values 
    ('a', 'a', false),
    (null, null, false),
    ('', null, true),
    (null, '', true),
    ('a', null, true),
    (null, 'b', true),
    ('a', 'b', true)
) q (v1, v2, expected)

returns

v1 | v2 | expected | is_different
---+----+----------+-------------
a  | a  | false    | false       
   |    | false    | false       
   |    | true     | true        
   |    | true     | true        
a  |    | true     | true        
   | b  | true     | true        
a  | b  | true     | true

You can make this even shorter by comparing a complete record which also removes the need for the OR

where 
   (my_table.some_uuid, my_table.some_text, my_table.some_int, my_table.some_date) 
       is distinct from 
   (excluded.some_uuid, excluded.some_text, excluded.some_int, excluded.some_date)

StackExchange Database Administrators Q#293847, answer score: 10