計數由 INSERT 語句引起的違反唯一約束

我想在 PostgreSQL 中添加行table2，table1看看有多少“重複鍵值違反唯一約束”類型的衝突。pkey是一個主鍵，所以它有一個唯一的約束。不幸conflict_action的是，顯然不允許增加計數器。

INSERT INTO table1 (pkey, col1, col2)
 SELECT pkey, col1, col2 FROM table2
 ON CONFLICT ...;

那麼是否有另一種（優雅的）方法可以通過計算或檢索該計數來解決這個問題？

插入不需要計數：

WITH
cte1 AS ( SELECT COUNT(*) cnt FROM table1 ),
cte2 AS ( SELECT COUNT(*) cnt FROM table2 ),
cte3 AS ( SELECT COUNT(*) cnt FROM table1 JOIN table2 USING (pkey) )
SELECT cte1.cnt "Records in table1",
      cte2.cnt "Records in table2",
      cte3.cnt "Conflicts count",
      cte2.cnt - cte3.cnt "Potential inserts count"
FROM cte1, cte2, cte3;

並且只插入不衝突的記錄——它會更便宜。

如果你真的想在 INSERT 完成後得到計數，你可以做這樣的事情。

with source_data as (
 SELECT pkey, col1, col2 
 FROM table2
), input_count as (
 select count(*) as source_count
 from source_data
), new_rows as (
 INSERT INTO table1 (pkey, col1, col2)
 select *
 from source_data
 ON CONFLICT DO NOTHING
 returning pkey
)
select source_count - count(*) as not_inserted
from new_rows
 cross join input_count;

雖然它不會非常有效。

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