我有一個 Postgres 表，在多個列上有一個唯一約束，其中之一可以是 NULL。對于每個組合，我只希望在該列中允許一個帶有 NULL 的記錄。
create table my_table (
col1 int generated by default as identity primary key,
col2 int not null,
col3 real,
col4 int,
constraint ux_my_table_unique unique (col2, col3)
);
我有一個 upsert 查詢，當它遇到 col2、col3 中具有相同值的記錄時，我想更新 col4：
insert into my_table (col2, col3, col4) values (p_col2, p_col3, p_col4)
on conflict (col2, col3) do update set col4=excluded.col4;
但是當 col3 為 NULL 時，沖突不會觸發。我已閱讀有關使用觸發器的資訊。請讓沖突發生的最佳解決方案是什么？
uj5u.com熱心網友回復：
如果您可以找到一個永遠不會合法存在的值col3（確保使用檢查約束），您可以使用唯一索引：
CREATE UNIQUE INDEX ON my_table (
col2,
coalesce(col3, -1.0)
);
并在您的INSERT：
INSERT INTO my_table (col2, col3, col4)
VALUES (p_col2, p_col3, p_col4)
ON CONFLICT (col2, coalesce(col3, -1.0))
DO UPDATE SET col4 = excluded.col4;
uj5u.com熱心網友回復：
NULL值不被視為彼此相等，因此永遠不會觸發UNIQUE違規。這意味著，您當前的表定義沒有按照您所說的去做。已經可以有多行了(col2, col3) = (1, NULL)。在您當前的設定中ON CONFLICT永遠不會觸發col3 IS NULL。
一個UNIQUE NULLS [NOT] DISTINCT供選擇UNIQUE的限制是在發展。但這充其量是在未來的 Postgres 15 中。
您可以按照此處的說明UNIQUE使用兩個部分UNIQUE索引強制執行約束：
- 使用空列創建唯一約束
適用于您的案例：
CREATE UNIQUE INDEX my_table_col2_uni_idx ON my_table (col2)
WHERE col3 IS NULL;
CREATE UNIQUE INDEX my_table_col2_col3_uni_idx ON my_table (col2, col3)
WHERE col3 IS NOT NULL;
但 ON CONFLICT ... DO UPDATE只能基于單個 UNIQUE索引或約束。只有ON CONFLICT DO NOTHING變體作為“包羅萬象”起作用。看：
- 如何在 PostgreSQL 中使用 RETURNING 和 ON CONFLICT？
它會看起來像你想要的是什么，目前不可能...
完美解決方案
不過，有一個完美的解決方案。有了兩個部分UNIQUE索引，您可以根據 的輸入值使用正確的陳述句col3：
WITH input(col2, col3, col4) AS (
VALUES
(3, NULL::real, 5) -- ①
, (3, 4, 5)
)
, upsert1 AS (
INSERT INTO my_table AS t(col2, col3, col4)
SELECT * FROM input WHERE col3 IS NOT NULL
ON CONFLICT (col2, col3) WHERE col3 IS NOT NULL -- matching index_predicate!
DO UPDATE
SET col4 = EXCLUDED.col4
WHERE t.col4 IS DISTINCT FROM EXCLUDED.col4 -- ②
)
INSERT INTO my_table AS t(col2, col3, col4)
SELECT * FROM input WHERE col3 IS NULL
ON CONFLICT (col2) WHERE col3 IS NULL -- matching index_predicate!
DO UPDATE SET col4 = EXCLUDED.col4
WHERE t.col4 IS DISTINCT FROM EXCLUDED.col4; -- ②
db<>在這里小提琴
適用于所有情況。
甚至適用于具有NULL和NOT NULL值的任意組合的多個輸入行col3。
并且甚至不會比普通陳述句花費更多，因為每一行只進入兩個 UPSERT 之一。
這是其中之一“Eurika！” 查詢一切都只是點擊，排除萬難。:)
① 注意::realCTE 中的顯式強制轉換input。這個相關的答案解釋了原因：
- 更新多行時強制轉換 NULL 型別
② The final WHERE clause is optional, but highly recommended. It would be a waste to go through with the UPDATE if it doesn't actually change anything. See:
- How do I (or can I) SELECT DISTINCT on multiple columns?
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/340194.html
標籤：sql PostgreSQL 空值 独特的 插入
下一篇：如何遞回檢索可用城鎮？