在批量插入期間使用 EXCEPTION 忽略重複項

在 pq 9.3.5 中，我從外部來源導入記錄，其中重複非常罕見，但確實會發生。給定一個readings具有唯一複合鍵 on的表(real_time_device_id, recorded_at)，以下操作將失敗一次：

INSERT INTO readings (real_time_device_id, recorded_at, duration) VALUES
 ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:33 -0700', 10.0),
 ... many more records ...
 ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:43 -0700', 10.0);

（FWIW，以上內容因重複密鑰違規而“正確”失敗。）

我知道處理異常很昂貴，但正如我所說，重複的條目非常罕見。因此，為了保持程式碼簡單，我遵循了Optimal way to ignore duplicate inserts 中給出的範例？：

BEGIN
   INSERT INTO readings (real_time_device_id, recorded_at, duration) VALUES
     ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:33 -0700', 10.0),
     ... many more records ...
     ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:43 -0700', 10.0);
EXCEPTION WHEN unique_violation THEN
 -- silently ignore inserts
END;

上面有兩個錯誤：

psql:sketches/t15.sql:11: ERROR:  syntax error at or near "INSERT"
LINE 2:         INSERT INTO readings (real_time_device_id, recorded_...
               ^
psql:sketches/t15.sql:14: ERROR:  syntax error at or near "EXCEPTION"
LINE 1: EXCEPTION WHEN unique_violation THEN
       ^

誰能告訴我正確的語法？還是我的錯誤比單純的語法更深？（例如，如果有一個重複項，是否會忽略所有 INSERT？）

更新的問題

正如@dezso 指出的那樣，上面的程式碼存在一些問題。因此（冒著將問題標記為過於籠統的風險），在很少 (< .1%) 重複的情況下進行批量插入的好方法是什麼？

有3 種可能的重複項：

1. 在批量插入的行內重複。那是您異常的直接原因。
2. 插入的行和現有行之間的重複。
3. 插入的行和來自其他事務的同時插入/更新的行之間的重複。

1.和2.可以很容易地固定。但是您確實需要準確定義如何解決衝突。選擇哪一行？

INSERT INTO readings (real_time_device_id, recorded_at, duration)
SELECT DISTINCT ON (real_time_device_id, recorded_at)  -- solves 1.
      i.real_time_device_id, i.recorded_at, i.dur
FROM (
   VALUES
 ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:33 -0700', 10.0),
 ('150', TIMESTAMP WITH TIME ZONE '2014-11-01 23:06:43 -0700', 10.0)
 ) i (real_time_device_id, recorded_at, dur)
LEFT   JOIN readings r1 USING (real_time_device_id, recorded_at)
WHERE  r1.real_time_device_id IS NULL                    -- solves 2.

我從每組騙局中選擇任意一行DISTINCT ON。您可能想要確定性地定義：

• 在每個 GROUP BY 組中選擇第一行？

**3.**更棘手-但希望不適用於您的情況…

要插入約 250K 行，將COPY批次插入臨時表（或者盡可能多地儲存在 RAM 中並在不溢出到磁碟的情況下進行處理）並從那裡繼續進行操作會更有效。

如果這是表的很大一部分，則可能需要刪除在更新期間不需要的索引並在之後創建它們……

• 通過 csv 導入大數據時出錯
• 多次呼叫UPSERT函式到不同的輸入
• 如何使用 Postgres 中的 CSV 文件中的值更新選定的行？

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