如何在PostgreSQL中结合使用ON CONFLICT和RETURNING? [英] How to use RETURNING with ON CONFLICT in PostgreSQL?

问题描述

我在PostgreSQL 9.5中具有以下UPSERT:

I have the following UPSERT in PostgreSQL 9.5:

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

如果没有冲突，则返回如下内容:

If there are no conflicts it returns something like this:

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

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

But if there are conflicts it doesn't return any rows:

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

如果没有冲突，我想返回新的id列，或者返回冲突列中现有的id列.
可以这样做吗?如果可以，如何?

I want to return the new id columns if there are no conflicts or return the existing id columns of the conflicting columns.
Can this be done? If so, how?

推荐答案

我遇到了完全相同的问题，即使我没有什么要更新，我还是使用执行更新"而不是不执行任何操作"解决了该问题.在您的情况下，将是这样的:

I had exactly the same problem, and I solved it using 'do update' instead of 'do nothing', even though I had nothing to update. In your case it would be something like this:

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;

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

This query will return all the rows, regardless they have just been inserted or they existed before.

这篇关于如何在PostgreSQL中结合使用ON CONFLICT和RETURNING?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！