Upsert是INSERT查询的扩展，在约束冲突的情况下可以用两种不同的行为定义:DO NOTHING或DO UPDATE。

INSERT INTO upsert_table VALUES (2, 6, 'upserted')
   ON CONFLICT DO NOTHING RETURNING *;

 id | sub_id | status
----+--------+--------
 (0 rows)

还要注意，RETURNING没有返回任何东西，因为没有插入任何元组。现在使用DO UPDATE，可以在与之冲突的元组上执行操作。首先请注意，定义一个约束是很重要的，它将用于定义存在冲突。

INSERT INTO upsert_table VALUES (2, 2, 'inserted')
   ON CONFLICT ON CONSTRAINT upsert_table_sub_id_key
   DO UPDATE SET status = 'upserted' RETURNING *;

 id | sub_id |  status
----+--------+----------
  2 |      2 | upserted
(1 row)

我修改了Erwin Brandstetter的惊人答案，它不会增加序列，也不会写锁任何行。我对PostgreSQL比较陌生，所以如果你看到这个方法的任何缺点，请随时告诉我:

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, new_rows AS (
   SELECT 
     c.usr
     , c.contact
     , c.name
     , r.id IS NOT NULL as row_exists
   FROM input_rows AS r
   LEFT JOIN chats AS c ON r.usr=c.usr AND r.contact=c.contact
   )
INSERT INTO chats (usr, contact, name)
SELECT usr, contact, name
FROM new_rows
WHERE NOT row_exists
RETURNING id, usr, contact, name

这假设表聊天对列(usr, contact)有唯一的约束。

更新:添加了来自spatar的建议修订(如下)。谢谢!

根据Revinand的评论，还有一个更新:

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, new_rows AS (
   INSERT INTO chats (usr, contact, name)
   SELECT 
     c.usr
     , c.contact
     , c.name
   FROM input_rows AS r
   LEFT JOIN chats AS c ON r.usr=c.usr AND r.contact=c.contact
   WHERE r.id IS NULL
   RETURNING id, usr, contact, name
   )
SELECT id, usr, contact, name, 'new' as row_type
FROM new_rows
UNION ALL
SELECT id, usr, contact, name, 'update' as row_type
FROM input_rows AS ir
INNER JOIN chats AS c ON ir.usr=c.usr AND ir.contact=c.contact

我没有测试上面的内容，但是如果您发现新插入的行被多次返回，那么您可以将UNION ALL更改为只是UNION，或者(更好)，完全删除第一个查询。

最简单、最高效的解决方案是

BEGIN;

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

SELECT id
FROM chats
WHERE (user, contact) IN (($1, $2), ($2, $1));

COMMIT;

DO UPDATE WHERE false锁定但不更新行，这是一个特性，而不是一个错误，因为它确保了另一个事务不能删除行。

有些注释想要区分更新的行和创建的行。

在这种情况下，只需将创建的txid_current() = xmin AS添加到select。

Upsert是INSERT查询的扩展，在约束冲突的情况下可以用两种不同的行为定义:DO NOTHING或DO UPDATE。

INSERT INTO upsert_table VALUES (2, 6, 'upserted')
   ON CONFLICT DO NOTHING RETURNING *;

 id | sub_id | status
----+--------+--------
 (0 rows)

还要注意，RETURNING没有返回任何东西，因为没有插入任何元组。现在使用DO UPDATE，可以在与之冲突的元组上执行操作。首先请注意，定义一个约束是很重要的，它将用于定义存在冲突。

INSERT INTO upsert_table VALUES (2, 2, 'inserted')
   ON CONFLICT ON CONSTRAINT upsert_table_sub_id_key
   DO UPDATE SET status = 'upserted' RETURNING *;

 id | sub_id |  status
----+--------+----------
  2 |      2 | upserted
(1 row)

目前接受的答案似乎是一个单一的冲突目标，少数冲突，小元组和没有触发器。它用蛮力避免了并发问题1(见下文)。简单的解决方案有它的吸引力，副作用可能不那么重要。

但是，对于所有其他情况，不需要更新相同的行。即使你从表面上看不出有什么不同，也有各种副作用:

It might fire triggers that should not be fired. It write-locks "innocent" rows, possibly incurring costs for concurrent transactions. It might make the row seem new, though it's old (transaction timestamp). Most importantly, with PostgreSQL's MVCC model UPDATE writes a new row version for every target row, no matter whether the row data changed. This incurs a performance penalty for the UPSERT itself, table bloat, index bloat, performance penalty for subsequent operations on the table, VACUUM cost. A minor effect for few duplicates, but massive for mostly dupes.

另外，有时使用ON冲突DO UPDATE是不实际的，甚至是不可能的。手册:

对于ON冲突DO UPDATE，必须提供一个conflict_target。

如果涉及多个索引/约束，单个“冲突目标”是不可能的。但这里有一个多部分索引的相关解决方案:

基于NULL值UNIQUE约束的UPSERT

回到主题，你可以(几乎)达到相同的效果，而不会出现空洞的更新和副作用。下面的一些解决方案也适用于ON CONFLICT DO NOTHING(没有“冲突目标”)，以捕捉所有可能出现的冲突——这可能是可取的，也可能不是可取的。

没有并发写负载

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id  --, usr, contact              -- return more columns?
   )
SELECT 'i' AS source                           -- 'i' for 'inserted'
     , id  --, usr, contact                    -- return more columns?
FROM   ins
UNION  ALL
SELECT 's' AS source                           -- 's' for 'selected'
     , c.id  --, usr, contact                  -- return more columns?
FROM   input_rows
JOIN   chats c USING (usr, contact);           -- columns of unique index

source列是可选添加的，用于演示这是如何工作的。实际上，您可能需要它来区分这两种情况(相对于空写入的另一个优点)。

最后的JOIN聊天可以工作，因为从附加的数据修改CTE新插入的行在底层表中还不可见。(同一个SQL语句的所有部分都看到底层表的相同快照。)

由于VALUES表达式是独立的(不直接附加到INSERT)， Postgres不能从目标列派生数据类型，您可能必须添加显式的类型强制转换。手册:

当在INSERT中使用VALUES时，所有值都是自动的 强制转换为相应目标列的数据类型。当 在其他上下文中使用时，可能有必要指定 正确的数据类型。如果条目都是带引号的文字常量， 强制使用第一个方法就足以确定所有假设的类型。

由于CTE的开销和额外的SELECT(这应该很便宜，因为完美的索引已经定义在那里了——索引实现了唯一的约束)，查询本身(不包括副作用)对于一些被欺骗的人来说可能会更昂贵一些。

对于许多副本可能(快得多)。额外写操作的有效成本取决于许多因素。

但无论如何，副作用和隐性成本都更少。总体来说可能更便宜。

附加的序列仍然是高级的，因为默认值是在测试冲突之前填写的。

ct:

SELECT类型查询是唯一可以嵌套的类型吗? 在关系除法中重复删除SELECT语句

具有并发写负载

假设默认READ COMMITTED事务隔离。相关:

并发事务导致在插入上有唯一约束的竞态条件

抵御竞争条件的最佳策略取决于确切的需求、表和UPSERTs中的行数和大小、并发事务的数量、冲突的可能性、可用资源和其他因素……

并发问题1

如果一个并发事务已经写入了一行，而您的事务现在试图UPSERT，那么您的事务必须等待另一个事务完成。

如果另一个事务以ROLLBACK(或任何错误，即自动ROLLBACK)结束，您的事务可以正常进行。次要可能的副作用:连续数字的差距。但是没有漏行。

如果其他事务正常结束(隐式或显式的COMMIT)， INSERT将检测到冲突(UNIQUE索引/约束是绝对的)并且DO NOTHING，因此也不返回行。(也不能像下面并发问题2中演示的那样锁定行，因为它不可见。)SELECT从查询开始时看到相同的快照，也不能返回尚不可见的行。

结果集中没有任何这样的行(即使它们存在于底层表中)!

这可能是可以的。特别是如果您不像示例中那样返回行，并且满足于知道行在那里。如果这还不够好，还有很多方法可以解决这个问题。

您可以检查输出的行数，如果它与输入的行数不匹配，则重复该语句。可能对罕见的情况来说足够了。重点是启动一个新的查询(可以在同一个事务中)，然后该查询将看到新提交的行。

或者在同一查询中检查缺失的结果行，并使用Alextoni的回答中演示的蛮力技巧覆盖这些结果行。

WITH input_rows(usr, contact, name) AS ( ... )  -- see above
, ins AS (
   INSERT INTO chats AS c (usr, contact, name) 
   SELECT * FROM input_rows
   ON     CONFLICT (usr, contact) DO NOTHING
   RETURNING id, usr, contact                   -- we need unique columns for later join
   )
, sel AS (
   SELECT 'i'::"char" AS source                 -- 'i' for 'inserted'
        , id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's'::"char" AS source                 -- 's' for 'selected'
        , c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (                                      -- RARE corner case
   INSERT INTO chats AS c (usr, contact, name)  -- another UPSERT, not just UPDATE
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel   s USING (usr, contact)     -- columns of unique index
   WHERE  s.usr IS NULL                         -- missing!
   ON     CONFLICT (usr, contact) DO UPDATE     -- we've asked nicely the 1st time ...
   SET    name = c.name                         -- ... this time we overwrite with old value
   -- SET name = EXCLUDED.name                  -- alternatively overwrite with *new* value
   RETURNING 'u'::"char" AS source              -- 'u' for updated
           , id  --, usr, contact               -- return more columns?
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;

它类似于上面的查询，但在返回完整的结果集之前，我们使用CTE ups再添加了一个步骤。最后一个CTE在大多数情况下不会起任何作用。只有当返回的结果中缺少行时，我们才使用暴力。

还有更多的开销。与已存在的行冲突越多，这种方法就越有可能优于简单方法。

一个副作用是:第2个UPSERT按顺序写行，因此如果向同一行写的三个或更多事务重叠，就会重新引入死锁的可能性(见下文)。如果这是一个问题，你需要一个不同的解决方案——比如重复上面提到的整个陈述。

并发问题2

如果并发事务可以写入受影响行的相关列，并且你必须确保你发现的行在同一事务的后面阶段仍然存在，你可以用以下方法将现有的行廉价地锁定在CTE in中(否则将被解锁):

...
ON CONFLICT (usr, contact) DO UPDATE
SET name = name WHERE FALSE  -- never executed, but still locks the row
...

并在SELECT中添加一个锁定子句，如FOR UPDATE。

这使得相互竞争的写操作一直等到事务结束，也就是所有锁都被释放的时候。所以要简短。

详情及解释:

如何在return from INSERT中包含被排除的行…在冲突 函数中的SELECT或INSERT是否容易出现竞态条件?

死锁?

通过以一致的顺序插入行来防止死锁。看到的:

多行插入导致死锁，尽管存在冲突，什么也不做

数据类型和类型转换

作为数据类型模板的现有表…

对独立VALUES表达式中的第一行数据进行显式类型强制转换可能不方便。有很多方法可以解决这个问题。您可以使用任何现有的关系(表、视图等)作为行模板。目标表是用例的明显选择。输入数据会自动转换为适当的类型，就像INSERT的VALUES子句一样:

WITH input_rows AS (
  (SELECT usr, contact, name FROM chats LIMIT 0)  -- only copies column names and types
   UNION ALL
   VALUES
      ('foo1', 'bar1', 'bob1')  -- no type casts here
    , ('foo2', 'bar2', 'bob2')
   )
   ...

这对某些数据类型不起作用。看到的:

更新多行时转换NULL类型

．.． 和名称

这也适用于所有数据类型。

在插入表的所有(前导)列时，可以省略列名。假设例子中的表聊天只包含UPSERT中使用的3列:

WITH input_rows AS (
   SELECT * FROM (
      VALUES
      ((NULL::chats).*)         -- copies whole row definition
      ('foo1', 'bar1', 'bob1')  -- no type casts needed
    , ('foo2', 'bar2', 'bob2')
      ) sub
   OFFSET 1
   )
   ...

---

旁白:不要使用像“用户”这样的保留词作为标识符。那是一把上膛的脚枪。使用合法、小写、不带引号的标识符。我把它换成了usr。

对于单个项的插入，我可能会在返回id时使用一个coalesce:

WITH new_chats AS (
    INSERT INTO chats ("user", "contact", "name")
    VALUES ($1, $2, $3)
    ON CONFLICT("user", "contact") DO NOTHING
    RETURNING id
) SELECT COALESCE(
    (SELECT id FROM new_chats),
    (SELECT id FROM chats WHERE user = $1 AND contact = $2)
);

对于多个项的插入，你可以把这些值放在一个临时WITH中，并在以后引用它们:

WITH chats_values("user", "contact", "name") AS (
    VALUES ($1, $2, $3),
           ($4, $5, $6)
), new_chats AS (
    INSERT INTO chats ("user", "contact", "name")
    SELECT * FROM chat_values
    ON CONFLICT("user", "contact") DO NOTHING
    RETURNING id
) SELECT id
    FROM new_chats
   UNION
  SELECT chats.id
    FROM chats, chats_values
   WHERE chats.user = chats_values.user
     AND chats.contact = chats_values.contact;

注意:根据Erwin的评论，在您的应用程序将尝试'upsert'相同的数据并发(两个worker尝试同时插入<unique_field> = 1)，并且这样的数据还不存在于表中，您应该在运行'upsert'之前更改事务的隔离级别:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

在这种特定情况下，两个事务中的一个将被中止。如果这种情况在您的应用程序中经常发生，您可能希望只执行2个单独的查询，否则，处理错误并重新执行查询会更容易、更快。