如何使用 PostgreSQL 的 ON CONFLICT 检索已插入行和冲突行的 ID？

在发生冲突时从 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 包含要更新插入的数据。

说明：

1. existing_rows CTE: 这将选择 id 表中与 chats 中的 ("user", "contact") 对匹配的 temp_table 预先存在的行。

2. inserted_rows CTE: 这将使用 INSERT 和 ON CONFLICT DO NOTHING 执行 RETURNING id 语句，捕获新插入行的 id。

3. UNION ALL: 这结合了两个 CTE 的结果，提供了受 upsert 操作影响的 id 的完整列表。

重要注意事项：

• 并发： 在高并发环境中，SELECT 和 INSERT 操作之间的数据可能会发生变化。 考虑使用事务或更强大的并发控制机制来保证数据完整性。
• 数据类型： 确保 temp_table 中的数据类型与 chats 表中的数据类型匹配。可能需要显式转换。
• 唯一性：保持temp_table数据的唯一性（或使用唯一标识符）以避免对同一行进行意外的多次更新。

这个改进的解决方案提供了一种更清晰、更高效的方法，用于在 PostgreSQL 更新插入操作期间检索所有相关 ID 并进行冲突处理。

以上是如何使用 PostgreSQL 的 ON CONFLICT 检索已插入行和冲突行的 ID？的详细内容。更多信息请关注PHP中文网其他相关文章！