如何在 Postgres 的主鍵上處理 UPDATE 中的重複項？

讓我們假設一個場景，其中包含實體Person和Company一個表格，該表格PersonCompanyStocks模擬了一個人擁有某家公司的股票數量（N:M 基數）。例如：

person | company | num_stocks
-----------------------------
Alice  | foo     | 300
Bob    | foo     | 100
Bob    | bar     | 200

該表用(person, company)作主鍵來保證唯一的條目，以及各個個人/公司表的外鍵（為簡單起見，我只使用字元串 ID）。

現在讓我們假設 companybar收購 company foo。我們希望以如下方式更新表：

person | company | num_stocks
-----------------------------
Alice  | bar     | 300
Bob    | bar     | 300

僅查看Alice’s 記錄建議使用天真的方法，例如：

UPDATE
 PersonCompanyStocks
SET
 company = "bar"
WHERE
 company = "foo"

但是，此更新失敗，duplicate key value violates unique constraint ...因為Bob已經有一行帶有 key ("Bob", "bar")。ForINSERT的 Postgres 支持ON CONFLICT DO ...，但似乎沒有對應的 for UPDATE。顯然，我們還必須處理正確合併num_stock兩行的值。

解決這個問題的最佳策略是什麼？我只看到一個相對難看的解決方案：

• 一個查詢來確定重複項。
• 一個UPDATE將重複項合併到最後一行。
• 一個DELETE刪除有問題的重複項。
• 以上UPDATE是在沒有重複的行中進行重命名。

這感覺很複雜，並且可能容易出現競爭條件。Postgres 是否提供任何技巧來更優雅地解決這個問題？

我們需要在公司之間轉移股票，對吧？這意味著我們需要向現有使用者添加股票並為新使用者更換公司。或者，同樣的，我們可以刪除公司“foo”的所有股票和公司“ insert .. on conflictbar”的新行

with rows as (                                             
 delete from PersonCompanyStocks 
   where company = 'foo'
   returning person, num_stocks
)
insert into PersonCompanyStocks (person, company, num_stocks) 
 select person, 'bar', num_stocks from rows
 on conflict(person,company) do update set 
   num_stocks = PersonCompanyStocks.num_stocks + excluded.num_stocks;

事務性的，由於刪除期間的行鎖定，這裡沒有競爭條件。

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