首頁 >資料庫 >mysql教程 >如何使用 PostgreSQL 的 ON CONFLICT 檢索已插入行和衝突行的 ID?

如何使用 PostgreSQL 的 ON CONFLICT 檢索已插入行和衝突行的 ID?

Linda Hamilton
Linda Hamilton原創
2025-01-21 18:21:08228瀏覽

How to Retrieve IDs of Inserted and Conflicting Rows Using PostgreSQL's ON CONFLICT?

發生衝突時從 PostgreSQL UPSERT 擷取 ID

挑戰:標準 PostgreSQL 9.5 ON CONFLICT DO NOTHINGRETURNING id 不會傳回衝突行的 ID。 目標是檢索更新插入操作期間涉及衝突的新插入行和現有行的 ID。

解:

這種增強的方法使用 WITH 子句來有效地處理此問題:

<code class="language-sql">WITH existing_rows AS (
    SELECT id
    FROM chats
    WHERE ("user", "contact") = ANY (SELECT ("user", "contact") FROM temp_table)
), inserted_rows AS (
    INSERT INTO chats ("user", "contact", "name")
    SELECT "user", "contact", "name"
    FROM temp_table
    ON CONFLICT ("user", "contact") DO NOTHING
    RETURNING id
)
SELECT id FROM existing_rows
UNION ALL
SELECT id FROM inserted_rows;</code>

temp_table 包含要更新插入的資料。

說明:

  1. existing_rows CTE: 這將選擇 id 表中與 chats 中的 ("user", "contact") 對匹配的 temp_table 預先存在的行。

  2. inserted_rows CTE: 這將使用 INSERTON CONFLICT DO NOTHING 執行 RETURNING id 語句,捕獲新插入行的 id

  3. UNION ALL: 這結合了兩個 CTE 的結果,提供了受 upsert 操作影響的 id 的完整列表。

重要注意事項:

  • 併發: 在高併發環境中,SELECTINSERT 操作之間的資料可能會改變。 考慮使用事務或更強大的並發控制機制來保證資料完整性。
  • 資料類型: 確保 temp_table 中的資料類型與 chats 表中的資料類型相符。可能需要顯式轉換。
  • 唯一性:保持temp_table資料的唯一性(或使用唯一識別碼)以避免對同一行進行意外的多次更新。

這個改良的解決方案提供了一種更清晰、更有效率的方法,用於在 PostgreSQL 更新插入作業期間檢索所有相關 ID 並進行衝突處理。

以上是如何使用 PostgreSQL 的 ON CONFLICT 檢索已插入行和衝突行的 ID?的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn