我在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列。
这可行吗？ 如果可以，怎么做？
目前接受的答案对于单个冲突目标、少量冲突、小元组和无触发器似乎还可以。它通过蛮力避免了并发问题1（见下文）。简单的解决方案具有吸引力，副作用可能不太重要。
然而，对于所有其他情况，请不要无需更新相同的行。即使表面上看不出任何差异，也会产生各种副作用：
它可能触发不应该触发的触发器。
它会对“无辜”的行进行写锁定，可能会给并发事务带来成本。
它可能使行看起来是新的，尽管它是旧的（事务时间戳）。
最重要的是，根据PostgreSQL的MVCC模型，无论行数据是否更改，UPDATE都会为每个目标行写入一个新的行版本。这会导致UPSERT本身的性能损失，表膨胀，索引膨胀，对表上后续操作的性能损失，以及VACUUM的成本。对于少量重复项，影响较小，但对于大多数重复项来说，影响非常大。
此外，有时候使用ON CONFLICT DO UPDATE并不切实际，甚至不可能。请参考手册：
ON CONFLICT DO UPDATE，必须提供一个conflict_target。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
源代码列是一个可选的附加项，用于演示它的工作原理。您可能实际上需要它来区分这两种情况（与空写入相比的另一个优点）。
最终的JOIN chats之所以有效，是因为附加的数据修改CTE中新插入的行在底层表中尚不可见。（同一SQL语句的所有部分都看到底层表的相同快照。）
由于VALUES表达式是独立的（不直接附加到INSERT语句），Postgres无法从目标列推导出数据类型，因此您可能需要添加显式类型转换。手册：
查询本身（不计算副作用）对于少量重复项可能会更昂贵，因为需要使用CTE和额外的SELECT（由于完美索引的存在，这应该是廉价的 - 唯一约束是通过索引实现的）。当VALUES用于INSERT时，值会自动强制转换为相应目标列的数据类型。当它用于其他上下文时，可能需要指定正确的数据类型。如果条目都是带引号的文字常量，则仅强制转换第一个条目就足以确定所有条目的假定类型。
READ COMMITTED事务隔离级别。相关链接：
防止竞争条件的最佳策略取决于确切的要求、表中行数和UPSERTs的大小、并发事务的数量、冲突的可能性、可用资源和其他因素...
如果一个并发事务已经对一行进行了写操作，而你的事务现在尝试进行UPSERT操作，你的事务必须等待另一个事务完成。
如果另一个事务以ROLLBACK（或任何错误，即自动ROLLBACK）结束，你的事务可以正常进行。可能的副作用：顺序号中的间隔，但不会有丢失的行。
或者在同一个查询中检查缺失的结果行，并使用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什么都不做。只有当返回结果中缺少行时，我们才会使用蛮力。
这会增加一些开销。与现有行发生冲突越多，这种方法的性能优于简单的方法的可能性就越大。
一个副作用是，第二个UPSERT会以无序的方式写入行，因此如果三个或更多事务同时写入相同的行，就会重新引入死锁的可能性（见下文）。如果这是一个问题，你需要一个不同的解决方案 - 就像上面提到的重复整个语句。
如果并发事务可以写入受影响行的相关列，并且您必须确保在同一事务的后期阶段找到的行仍然存在，您可以在CTE ins中以低成本的方式锁定现有行（否则将不会锁定）：
...
ON CONFLICT (usr, contact) DO UPDATE
SET name = name WHERE FALSE -- never executed, but still locks the row
...
SELECT中添加一个锁定子句，比如FOR UPDATE。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')
)
...
这对于所有数据类型都适用。
在插入表的所有（前导）列时，您可以省略列名。假设示例中的表chats只包含在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
)
...
ON CONFLICT SELECT... 这样的东西存在 :) - Roshambo我曾经遇到过完全相同的问题，我通过使用“do update”而不是“do nothing”来解决它，即使我没有任何需要更新的内容。在你的情况下，代码会像这样：
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;
这个查询将返回所有行，无论它们是刚插入的还是之前就存在。
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 CONFLICT DO NOTHING的主要目的是避免抛出错误，但会导致未返回任何行。因此，我们需要另一个SELECT来获取现有的id。SELECT将获取现有行；如果成功插入，则会有两个相同的记录，然后我们需要使用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中，然后稍后引用它们：
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”（两个工作线程同时插入一个相同的<unique_field> = 1），且在表中不存在这样的数据，那么在运行“upsert”之前，你应该更改事务的隔离级别:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
... SELECT COALESCE ( ... ) AS id - Madacolupsert是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)
那么你可以通过使用简单的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 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
Caused by: org.postgresql.util.PSQLException: ERROR: INSERT操作的目标列数大于表达式数量 Hint: 插入源是一个行表达式，其列数与INSERT所需的相同。您是否意外多使用了括号？ - ka3akextant CTE中使用SELECT FOR UPDATE来提前获取写锁。这将导致同一查询的并发执行在尝试选择现有行之前被阻塞，并且只允许在早期执行完成对表的写入后才能继续进行。 - Aristotle Pagaltzis在Erwin上面的回答基础上构建（非常好的答案，没有它我永远也做不到！），这就是我的最终结果。它解决了一些额外的潜在问题——通过对输入集合进行select distinct，它允许重复值（否则会出错），并确保返回的ID与输入集合完全匹配，包括相同的顺序和允许重复。
另外，对我来说很重要的一部分是，使用new_rows CTE大大减少了不必要的序列推进次数，仅尝试插入尚未存在的那些。考虑到并发写入的可能性，它仍然会在缩小的集合中遇到一些冲突，但后续步骤会处理这些冲突。在大多数情况下，序列间隙并不是一个大问题，但当您进行数十亿次upsert时，并且高百分比存在冲突时，它可以使使用ID的int或bigint之间的差异。
尽管它很大且丑陋，但它的表现非常出色。我进行了广泛的测试，包括数百万个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;
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 created 添加到选择语句中即可。SELECT中返回插入集合的ID，为什么还需要DO UPDATE..WHERE false和RETURNING子句呢？在PG 12中，如果没有UPDATE(根据WHERE false子句)，RETURNING子句仍然不返回任何内容。 - BrDaHaDO UPDATE WHERE false会锁定但不会更新行”，我理解了这一点。我想问的是，既然RETURNING子句实际上并没有返回任何内容，那么它为什么要存在呢？RETURNING子句是否也需要用于防止删除操作？ - BrDaHa我修改了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
chats在列(usr, contact)上有唯一约束条件。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，或者（更好的办法）完全删除第一个查询。我还没有测试上述方法。CASE WHEN r.id IS NULL THEN FALSE ELSE TRUE END AS row_exists，而是直接写成 r.id IS NOT NULL as row_exists。
不要使用 WHERE row_exists=FALSE，而是直接写成 WHERE NOT row_exists。 - spatar将字段更新为其当前值。在这种情况下，冲突行的数据不会更改，并且它将返回冲突行的 id 而不是空集。
INSERT INTO chats c ("user", "contact", "name")
VALUES ($1, $2, $3),
($2, $1, NULL)
ON CONFLICT("user", "contact") DO UPDATE
SET user = c.user
RETURNING id
ON CONFLICT UPDATE使行发生更改，然后使用RETURNING捕获它。 - Gordon Linoff