具有 NULL 值的 PostgreSQL UPSERT 問題

我在使用 Postgres 9.5 中的新 UPSERT 功能時遇到問題

我有一個用於聚合另一個表中的數據的表。複合鍵由 20 列組成，其中 10 列可以為空。下面我創建了我遇到的問題的一個較小版本，特別是 NULL 值。

CREATE TABLE public.test_upsert (
upsert_id serial,
name character varying(32) NOT NULL,
status integer NOT NULL,
test_field text,
identifier character varying(255),
count integer,
CONSTRAINT upsert_id_pkey PRIMARY KEY (upsert_id),
CONSTRAINT test_upsert_name_status_test_field_key UNIQUE (name, status, test_field)
);

根據需要執行此查詢（第一次插入，然後後續插入只是增加計數）：

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count) 
VALUES ('shaun',1,'test value','ident', 1)
ON CONFLICT (name,status,test_field) DO UPDATE set count = tu.count + 1 
where tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = 'test value';

但是，如果我執行此查詢，則每次插入 1 行，而不是增加初始行的計數：

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count) 
VALUES ('shaun',1,null,'ident', 1)
ON CONFLICT (name,status,test_field) DO UPDATE set count = tu.count + 1  
where tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = null;

這是我的問題。我需要簡單地增加計數值，而不是創建多個具有空值的相同行。

嘗試添加部分唯一索引：

CREATE UNIQUE INDEX test_upsert_upsert_id_idx
ON public.test_upsert
USING btree
(name COLLATE pg_catalog."default", status, test_field, identifier);

但是，這會產生相同的結果，要麼插入多個空行，要麼在嘗試插入時出現此錯誤消息：

錯誤：沒有與 ON CONFLICT 規範匹配的唯一或排除約束

我已經嘗試在部分索引上添加額外的細節，例如WHERE test_field is not null OR identifier is not null. 但是，插入時我收到約束錯誤消息。

澄清ON CONFLICT DO UPDATE行為

考慮這裡的手冊：

對於建議插入的每個單獨的行，插入繼續進行，或者，如果 conflict_target違反了指定的仲裁器約束或索引，則採用替代conflict_action方法。

大膽強調我的。因此，您不必對(the )WHERE子句中唯一索引中包含的列重複謂詞：UPDATE``conflict_action

INSERT INTO test_upsert AS tu
      (name   , status, test_field  , identifier, count) 
VALUES ('shaun', 1     , 'test value', 'ident'   , 1)
ON CONFLICT (name, status, test_field) DO UPDATE
SET count = tu.count + 1;
WHERE tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = 'test value'

獨特的違規已經確定了您添加的WHERE條款將多餘地強制執行。

明確部分索引

添加一個WHERE子句，使其成為您自己提到的實際部分索引（但使用反轉邏輯）：

CREATE UNIQUE INDEX test_upsert_partial_idx
ON public.test_upsert (name, status)
WHERE test_field IS NULL;  -- not: "is not null"

要在你的 UPSERT 中使用這個部分索引，你需要一個像 @ypercube 展示的匹配：conflict_target

ON CONFLICT (name, status) WHERE test_field IS NULL

現在推斷出上面的部分索引。但是，正如手冊還指出的那樣：

$$ … $$ON CONFLICT如果滿足所有其他條件的索引可用，則將推斷非部分唯一索引（沒有謂詞的唯一索引）（並因此被 使用）。

如果您有一個額外的（或唯一的）索引，(name, status)它將（也）被使用。不會(name, status, test_field)明確推斷出索引。這並不能解釋您的問題，但可能在測試時增加了混亂。

解決方案

AIUI，以上都沒有解決你的問題。使用部分索引，只會擷取具有匹配 NULL 值的特殊情況。如果您沒有其他匹配的唯一索引/約束，則將插入其他重複行，或者如果您這樣做，則會引發異常。我想這不是你想要的。你寫：

複合鍵由 20 列組成，其中 10 列可以為空。

你到底認為什麼是重複的？Postgres（根據 SQL 標準）不認為兩個 NULL 值相等。手冊：

通常，如果表中有不止一行，其中包含在約束中的所有列的值都相等，則違反了唯一約束。但是，在此比較中，兩個空值永遠不會被視為相等。這意味著即使存在唯一約束，也可以在至少一個受約束的列中儲存包含空值的重複行。此行為符合 SQL 標準，但我們聽說其他 SQL 數據庫可能不遵循此規則。因此，在開發旨在可移植的應用程序時要小心。

有關的：

• 允許在唯一列中為空

我假設您希望NULL所有 10 個可空列中的值都被視為相等。使用額外的部分索引覆蓋單個可為空的列是優雅且實用的，如下所示：

• PostgreSQL 多列唯一約束和 NULL 值

但是對於更多可以為空的列，這很快就會失控。對於可為空列的每個不同組合，您都需要一個部分索引。對於(a),(b)和的 3 個部分索引中的 2 個(a,b)。這個數字隨著 呈指數增長2^n - 1。對於您的 10 個可空列，要涵蓋 NULL 值的所有可能組合，您已經需要 1023 個部分索引。不去。

簡單的解決方案：替換 NULL 值並定義涉及的列NOT NULL，使用簡單的UNIQUE約束一切都可以正常工作。

如果這不是一個選項，我建議使用表達式索引COALESCE來替換索引中的 NULL：

CREATE UNIQUE INDEX test_upsert_solution_idx
   ON test_upsert (name, status, **COALESCE(test_field, '')**);

空字元串 ( '') 顯然是字元類型的候選對象，但您可以使用任何合法值，根據**您對“唯一”的定義，這些值要麼永遠不會出現，要麼可以與 NULL 折疊。

然後使用這個語句：

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count) 
VALUES ('shaun', 1, null        , 'ident', 11)  -- works with
    , ('bob'  , 2, 'test value', 'ident', 22)  -- and without NULL
ON     CONFLICT (name, status, COALESCE(test_field, '')) DO UPDATE  -- match expr. index
SET    count = COALESCE(tu.count + EXCLUDED.count, EXCLUDED.count, tu.count);

像@ypercube 一樣，我假設您實際上想要添加count到現有的count. 由於列可以為 NULL，因此添加 NULL 會將列設置為 NULL。如果定義count NOT NULL，則可以簡化。

或者稍後考慮這個，使用行的雜湊值的相關答案：

• 為什麼我的 UNIQUE 約束沒有觸發？

---

一個不同的想法是從語句中刪除衝突目標以涵蓋**所有獨特的違規行為。然後，您可以定義各種唯一索引，以更複雜地定義應該是“唯一”的內容。但這不會飛ON CONFLICT DO UPDATE。手冊再次：

對於ON CONFLICT DO NOTHING，指定一個衝突目標是可選的；省略時，將處理與所有可用約束（和唯一索引）的衝突。對於，必須ON CONFLICT DO UPDATE提供一個衝突目標。

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