使用 PostgreSQL 的 ON CONFLICT DO NOTHING with RETURNING 時如何擷取資料？

PostgreSQL 的 ON CONFLICT DO NOTHING 和資料擷取

將 PostgreSQL 的 ON CONFLICT DO NOTHING 子句與 RETURNING 一起使用時，發生衝突時會傳回空結果。 這是因為 DO NOTHING 不會更新或傳回任何行。 本文探討了即使發生衝突也能檢索資料的解決方案。

現有方法的挑戰與限制

現有方法通常針對單一衝突目標和簡單條件來解決這個問題。然而，這些方法可能有其限制和潛在的副作用。

強大的資料擷取解決方案

我們提出了兩種改進的方法來有效處理衝突並確保資料檢索，解決並發寫入場景：

1。 在沒有並發寫入的情況下處理衝突

此方法使用公用表表達式 (CTE) 來分隔 INSERT 和 SELECT 操作。 結果使用 UNION ALL.

組合

<code class="language-sql">WITH input_rows(usr, contact, name) AS (
   VALUES
      ('foo1', 'bar1', 'bob1')
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id
   )
SELECT 'i' AS source, id
FROM   ins
UNION  ALL
SELECT 's' AS source, c.id
FROM   input_rows
JOIN   chats c USING (usr, contact);</code>

該查詢首先嘗試插入。 然後，它選擇與輸入資料相符的現有行。 source 欄位指示是否插入行 ('i') 或選取行 ('s')。

2。 處理併發寫入的衝突

這個更強大的解決方案可以解決並發寫入操作。 它檢查查詢中是否有缺少的行，並使用額外的 UPSERT 來處理它們。

<code class="language-sql">WITH input_rows(usr, contact, name) AS ( ... )  -- as above
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id, usr, contact
   )
, sel AS (
   SELECT 'i' AS source, id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's' AS source, c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (
   INSERT INTO chats (usr, contact, name)
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel s USING (usr, contact)
   WHERE  s.usr IS NULL
   ON CONFLICT (usr, contact) DO UPDATE
   SET    name = c.name  -- or EXCLUDED.name
   RETURNING 'u' AS source, id
   )
SELECT source, id FROM sel
UNION  ALL
SELECT * FROM ups;</code>

此方法可確保傳回所有插入或更新的行，無論是否有衝突或併發更新。 ups CTE 處理初始 INSERT 中遺漏的任何行。 source 列區分插入的 ('i')、選定的 ('s') 和更新的 ('u') 行。 這為在各種條件下檢索資料提供了全面的解決方案。

以上是使用 PostgreSQL 的 ON CONFLICT DO NOTHING with RETURNING 時如何擷取資料？的詳細內容。更多資訊請關注PHP中文網其他相關文章！