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

我修改了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)有唯一的约束。

更新:添加了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，或者(更好)，完全删除第一个查询。

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。

在此SQL中，如果它在冲突上失败，则它将不返回任何内容，然后第二个SELECT将获得现有行;如果它成功插入，那么将有两条相同的记录，然后我们需要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 created添加到select。

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

此外，对我来说很重要的一部分，它显著减少不必要的序列进度使用new_rows CTE只尝试插入那些还没有在那里。考虑到并发写入的可能性，它仍然会在这个简化集中遇到一些冲突，但后面的步骤将解决这个问题。在大多数情况下，序列间隙不是什么大问题，但当你做数十亿个upserts时，冲突的比例很高，它可能会导致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;

如果您只想插入一行

然后你可以通过使用简单的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