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

Question

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 coalesce('a', '') <> coalesce(null, '')  --> true

That is, it is only true when the value actually changed. However, what happens if a value genuinely is empty string ''? Then it won't update.

This means we need to be creative about choosing the dummy value '' such that it isn't a value which would naturally occur. We could just invent a keyword which is unlikely to occur in production. But I'd rather find another pattern which doesn't have this drawback.

What options exist to do this susinclty giving the same change "truth table" I showed above? We could always use case when ..., but it becomes extremely verbose. We need something which is easy to write and easy to read. A row can often include 5-15 value columns

Are there any alternatives which could do an upsert without the drawback of the pattern we are using today?

---

The following can be used as a testbed to find a suitable pattern:

select
    v1, v2, expected,
    COALESCE(v1, '') <> COALESCE(v2, '') as current_version,
    COALESCE(v1 <> v2, true) as candidate_version
from (
    select 'a' as v1, 'a' as v2, false as expected
    union all
    select null as v1, null as v2, false as expected
    union all
    select '' as v1, null as v2, true as expected
    union all
    select null as v1, '' as v2, true as expected
    union all
    select 'a' as v1, null as v2, true as expected
    union all
    select null as v1, 'b' as v2, true as expected
    union all
    select 'a' as v1, 'b' as v2, true as expected
) q

Returning:

v1     v2     expected current_version candidate_version
a      a      false    false           false
null   null   false    false           true
''     null   true     false           true
null   ''     true     false           true
a      null   true     true            true
null   b      true     true            true
a      b      true     true            true

Answer 1

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)

Answer 2

Have tried using IS DISTINCT FROM?

SELECT ...
    FROM ...
    WHERE my_table.some_uuid IS DISTINCT FROM excluded.some_uuid
        OR (my_table.some_text IS DISTINCT FROM excluded.some_text
        ...