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来合并结果。

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

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来合并结果。

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

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。

我也遇到了同样的问题，我使用“做更新”而不是“什么都不做”来解决它，尽管我没有什么可更新的。在你的情况下，它会是这样的:

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;

这个查询将返回所有的行，无论它们是刚刚插入的还是之前已经存在的。

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)