Como detectar "sucintamente" valores alterados na cláusula upsert do PostgreSQL (em conflito) where enquanto suporta alterações nulas?

Mantemos um grande data warehouse implementado em PostgreSQL e python. Um padrão muito comum que fazemos é criar upserts e registrar quando as coisas foram atualizadas. Temos algumas chaves my_keye valores exclusivos, digamos, my_uuid, my_text, my_int, my_date. Se algum desses valores for alterado para um determinado my_key, gostaríamos de atualizar a linha. Tudo bem, e temos um padrão que funciona bem:

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)

A última on conflict ... wherecláusula é importante, pois garante que o update_timestamparquivo só seja atualizado quando houver alterações. Também garante que não atualizamos linhas desnecessariamente, melhorando o desempenho.

De qualquer forma, muitas vezes temos um problema com a coalesce()lógica. A razão pela qual existe neste padrão é para suportar o valor indo e vindo de null. Tomemos o seguinte exemplo:

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

Isso funciona bem e produz os seguintes resultados para uma lista abrangente de casos de teste:

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

Ou seja, só é verdade quando o valor realmente mudou. No entanto, o que acontece se um valor for genuinamente uma string vazia ''? Então não atualiza.

Isso significa que precisamos ser criativos ao escolher o valor fictício de ''modo que não seja um valor que ocorreria naturalmente. Poderíamos simplesmente inventar uma palavra-chave que dificilmente ocorreria em produção. Mas prefiro encontrar outro padrão que não tenha essa desvantagem.

Quais opções existem para fazer essa susinclty dando a mesma alteração "truth table" que mostrei acima? Sempre poderíamos usar case when ..., mas se torna extremamente detalhado. Precisamos de algo que seja fácil de escrever e fácil de ler. Uma linha geralmente pode incluir de 5 a 15 colunas de valor

Existem alternativas que poderiam fazer um upsert sem a desvantagem do padrão que estamos usando hoje?

O seguinte pode ser usado como um testbed para encontrar um padrão adequado:

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

Retornando:

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