如何在支持空更改的同時“簡潔地”檢測 PostgreSQL 的 upsert（衝突時）where 子句中的更改值？

我們維護一個用 PostgreSQL 和 python 實現的大型數據倉庫。我們做的一種非常常見的模式是進行更新插入，並記錄更新的時間。我們有一些獨特的鍵my_key和值，比如my_uuid, my_text, my_int, my_date. 如果給定的任何這些值發生變化，my_key我們希望更新該行。沒關係，我們有一個執行良好的模式：

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)

最後一個on conflict ... where子句很重要，因為它確保update_timestamp只有在發生更改時才更新。它還確保我們不會不必要地更新行，從而提高性能。

無論如何，我們經常遇到coalesce()邏輯問題。它存在於這種模式中的原因是支持去往和來自 的值null。讓我們看下面的例子：

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

這可以正常工作，並為完整的測試案例列表生成以下結果：

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

也就是說，只有當值實際改變時才成立。但是，如果一個值真的是空字元串會發生什麼''？然後就不會更新了。

這意味著我們需要創造性地選擇虛擬值''，使其不是自然發生的值。我們可以發明一個在生產中不太可能出現的關鍵字。但我寧願找到另一種沒有這個缺點的模式。

有哪些選項可以做到這一點，給出我上面顯示的相同變化“真值表”？我們總是可以使用case when ...，但它變得非常冗長。我們需要一些易於編寫和易於閱讀的東西。一行通常可以包含 5-15 個值列

是否有任何替代方案可以在沒有我們今天使用的模式的缺點的情況下進行 upsert？

---

以下可用作尋找合適模式的測試平台：

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

返回：

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

您可以使用is distinct from提到的 gsiems，它是 null 安全的“不等於”運算符。null is distinct from null是假的，而且42 is distinct from null是真的。

您的測試平台：

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)

返回

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                 

您可以通過比較完整的記錄來縮短它，這也消除了對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)

引用自：https://dba.stackexchange.com/questions/293847