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上面的回答(顺便说一句，这是一个很棒的答案，如果没有它，我永远不会走到这里!)，这就是我最终的结果。它解决了两个额外的潜在问题——通过对输入集执行不同的选择，它允许重复(否则会抛出错误)，并且它确保返回的id与输入集完全匹配，包括相同的顺序和允许重复。

Additionally, and one part that was important for me, it significantly reduces the number of unnecessary sequence advancements using the new_rows CTE to only try inserting the ones that aren't already in there. Considering the possibility of concurrent writes, it will still hit some conflicts in that reduced set, but the later steps will take care of that. In most cases, sequence gaps aren't a big deal, but when you're doing billions of upserts, with a high percentage of conflicts, it can make the difference between using an int or a bigint for the ID.

尽管它又大又丑，但性能非常好。我用数百万个upserts、高并发性和大量冲突对它进行了广泛测试。坚如磐石。

我将其打包为一个函数，但如果这不是您想要的，那么应该很容易看到如何将其转换为纯SQL。我还将示例数据更改为简单的内容。

CREATE TABLE foo
(
  bar varchar PRIMARY KEY,
  id  serial
);
CREATE TYPE ids_type AS (id integer);
CREATE TYPE bars_type AS (bar varchar);

CREATE OR REPLACE FUNCTION upsert_foobars(_vals bars_type[])
  RETURNS SETOF ids_type AS
$$
BEGIN
  RETURN QUERY
    WITH
      all_rows AS (
        SELECT bar, ordinality
        FROM UNNEST(_vals) WITH ORDINALITY
      ),
      dist_rows AS (
        SELECT DISTINCT bar
        FROM all_rows
      ),
      new_rows AS (
        SELECT d.bar
        FROM dist_rows d
             LEFT JOIN foo f USING (bar)
        WHERE f.bar IS NULL
      ),
      ins AS (
        INSERT INTO foo (bar)
          SELECT bar
          FROM new_rows
          ORDER BY bar
          ON CONFLICT DO NOTHING
          RETURNING bar, id
      ),
      sel AS (
        SELECT bar, id
        FROM ins
        UNION ALL
        SELECT f.bar, f.id
        FROM dist_rows
             JOIN foo f USING (bar)
      ),
      ups AS (
        INSERT INTO foo AS f (bar)
          SELECT d.bar
          FROM dist_rows d
               LEFT JOIN sel s USING (bar)
          WHERE s.bar IS NULL
          ORDER BY bar
          ON CONFLICT ON CONSTRAINT foo_pkey DO UPDATE
            SET bar = f.bar
          RETURNING bar, id
      ),
      fin AS (
        SELECT bar, id
        FROM sel
        UNION ALL
        TABLE ups
      )
    SELECT f.id
    FROM all_rows a
         JOIN fin f USING (bar)
    ORDER BY a.ordinality;
END
$$ LANGUAGE plpgsql;

我修改了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，或者(更好)，完全删除第一个查询。

对于单个项的插入，我可能会在返回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个单独的查询，否则，处理错误并重新执行查询会更容易、更快。

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

使用ON冲突DO NOTHING的主要目的是避免抛出错误，但它不会导致行返回。因此，我们需要另一个SELECT来获取现有的id。

在这个SQL中，如果它在冲突上失败，它将不返回任何东西，然后第二个SELECT将获得现有的行;如果插入成功，那么将会有两条相同的记录，然后我们需要UNION来合并结果。

目前接受的答案似乎是一个单一的冲突目标，少数冲突，小元组和没有触发器。它用蛮力避免了并发问题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。