使用 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中文网其他相关文章！