發生衝突時從 PostgreSQL UPSERT 擷取 ID
挑戰:標準 PostgreSQL 9.5 ON CONFLICT DO NOTHING
和 RETURNING 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
包含要更新插入的資料。
說明:
existing_rows
CTE: 這將選擇 id
表中與 chats
中的 ("user", "contact")
對匹配的 temp_table
預先存在的行。
inserted_rows
CTE: 這將使用 INSERT
和 ON CONFLICT DO NOTHING
執行 RETURNING id
語句,捕獲新插入行的 id
。
UNION ALL
: 這結合了兩個 CTE 的結果,提供了受 upsert 操作影響的 id
的完整列表。
重要注意事項:
SELECT
和 INSERT
操作之間的資料可能會改變。 考慮使用事務或更強大的並發控制機制來保證資料完整性。 temp_table
中的資料類型與 chats
表中的資料類型相符。可能需要顯式轉換。 temp_table
資料的唯一性(或使用唯一識別碼)以避免對同一行進行意外的多次更新。 這個改良的解決方案提供了一種更清晰、更有效率的方法,用於在 PostgreSQL 更新插入作業期間檢索所有相關 ID 並進行衝突處理。
以上是如何使用 PostgreSQL 的 ON CONFLICT 檢索已插入行和衝突行的 ID?的詳細內容。更多資訊請關注PHP中文網其他相關文章!