如何在 PostgreSQL 中使用 RETURNING 和 ON CONFLICT？

Question

我在 PostgreSQL 9.5 中有以下 UPSERT：

 INSERT INTO chats ("user", "contact", "name")
           VALUES ($1, $2, $3),
                  ($2, $1, NULL)
ON CONFLICT("user", "contact") DO NOTHING
RETURNING id;

如果没有冲突，它会返回如下内容：

 ----------
    | id |
----------
  1 | 50 |
----------
  2 | 51 |
----------

但如果有冲突，它不会返回任何行：

 ----------
    | id |
----------

如果没有冲突，我想返回新的 id 列，或者返回冲突列的现有 id 列。

这可以做到吗？ 如果是这样， 怎么做？

原文由 zola 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

我遇到了完全相同的问题，我使用“做更新”而不是“什么都不做”来解决它，即使我没有什么要更新的。在你的情况下，它会是这样的：

 INSERT INTO chats ("user", "contact", "name")
       VALUES ($1, $2, $3),
              ($2, $1, NULL)
ON CONFLICT("user", "contact")
DO UPDATE SET
    name=EXCLUDED.name
RETURNING id;

此查询将返回所有行，无论它们是刚刚插入还是之前存在。

原文由 Alextoni 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

如果您只想插入一行

然后，您可以通过使用简单的 EXISTS 检查来显着简化事情：

 WITH
  extant AS (
    SELECT id FROM chats WHERE ("user", "contact") = ($1, $2)
  ),
  inserted AS (
    INSERT INTO chats ("user", "contact", "name")
    SELECT $1, $2, $3
    WHERE NOT EXISTS (SELECT NULL FROM extant)
    RETURNING id
  )
SELECT id FROM inserted
UNION ALL
SELECT id FROM extant

由于没有 ON CONFLICT 子句，因此没有更新 - 只有插入，并且仅在必要时。所以没有不必要的更新，没有不必要的写锁，没有不必要的序列增量。也不需要演员表。

如果写锁定是您的用例中的一项功能，您可以在 extant 表达式中使用 SELECT FOR UPDATE —。

如果您需要知道是否插入了新行，可以在顶层 UNION 中添加一个标志列：

 SELECT id, TRUE AS inserted FROM inserted
UNION ALL
SELECT id, FALSE FROM extant

原文由 Aristotle Pagaltzis 发布，翻译遵循 CC BY-SA 4.0 许可协议