避免在 PostgreSQL 中將要插入的行中的重複

我有一個目標表a和一組源表b和c. 我正在嘗試對afromb和進行插入c，並另外檢查以下組合中(column1, column2)是否不存在a：

INSERT INTO a (column1, column2)
SELECT column1, column2 FROM b
UNION ALL
SELECT column1, column2 FROM c
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.column1 = column1 AND a.column2 = column2);

不幸的是，由於in上存在唯一索引，b並且c可能包含具有相同組合的多行，因此很有可能(column1, column2)會產生重複的鍵值違規。(column1, column2)``a

我找到了兩個解決方案（PostgreSQL v13）：

1. 替換UNION ALL為UNIONwhich 忽略重複項。
2. INSERT與結合ON CONFLICT DO NOTHING。

上述解決方案有一些缺點（對我來說），例如第一個將對數據進行排序，而第二個將在主鍵值中產生間隙。我正在尋找第三種解決方案。NOT EXISTS (SELECT 1 FROM a WHERE ...)條件只訪問a之前（即事務之前）的內容INSERT，是否有可能訪問由添加但尚未送出的內容？NOT EXISTS (...)``a``INSERT

首先，如果你想同時檢查b和 的c行a，你應該WHERE在每個之後都有一個子句SELECT。目前您只檢查c行，因為WHERE查詢末尾的 僅適用於第二UNION條腿。

因此，您的查詢可能應該像這樣修改：

INSERT INTO a (column1, column2)
SELECT column1, column2 FROM b
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.column1 = b.column1 AND a.column2 = b.column2)
UNION ALL
SELECT column1, column2 FROM c
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.column1 = c.column1 AND a.column2 = c.column2);

其次，為了確保這些c行排除 的任何重複項b，您可以添加另一個NOT EXISTS檢查：

INSERT INTO a (column1, column2)
SELECT column1, column2 FROM b
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.column1 = b.column1 AND a.column2 = b.column2)
UNION ALL
SELECT column1, column2 FROM c
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.column1 = c.column1 AND a.column2 = c.column2)
 AND NOT EXISTS (SELECT 1 FROM b WHERE b.column1 = c.column1 AND b.column2 = c.column2);

然而，以上所有對我來說似乎有點過於復雜了。我只想做這樣簡單的事情：

INSERT INTO a (column1, column2)
SELECT column1, column2 FROM b
UNION
SELECT column1, column2 FROM c
EXCEPT
SELECT column1, column2 FROM a;

或者，如果我想盡可能明確地表達我的意圖，那麼：

INSERT INTO a (column1, column2)
(
SELECT column1, column2 FROM b
UNION
SELECT column1, column2 FROM c
)
EXCEPT
SELECT column1, column2 FROM a;

儘管您的要求不是一個好的做法，但如果這是您的業務需求，那麼請嘗試使用公用表表達式進行此技巧。

由於您想避免記錄的順序跳過和重新排序，因此在第一步中，我們將分配row_number()給每個記錄以及表程式碼。

在第二步中，我們將使用刪除結果集中的所有重複記錄distinct on。

在最後一步中，我們將使用not exists子句過濾表 a 中已經存在的記錄，並在最終插入表 a 之前按表鍵和行號重新排序。

with cte as (
SELECT 'b' "tab",row_number() over() "seq", column1, column2 FROM b
UNION all
SELECT 'c',row_number() over(),column1, column2 FROM c
),
cte1 as (
select distinct on (column1,column2) * from cte 
where not exists (select 1 from a where column1=cte.column1 and column2=cte.column2)
order by 3,4
)
insert into a(column1,column2)
select column1,column2 from cte1 order by tab,seq

展示

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