我有以下UPSERT在PostgreSQL 9.5:
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(见下文)。简单的解决方案有它的吸引力，副作用可能不那么重要。
但是，对于所有其他情况，不需要更新相同的行。即使你从表面上看不出有什么不同，也有各种副作用:
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。
其他回答
目前接受的答案似乎是一个单一的冲突目标，少数冲突，小元组和没有触发器。它用蛮力避免了并发问题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个单独的查询，否则，处理错误并重新执行查询会更容易、更快。
我修改了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，或者(更好)，完全删除第一个查询。
如果您只想插入一行
然后你可以使用一个简单的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子句，所以没有更新——只有一个插入，而且只有在必要时才有。所以没有不必要的更新，没有不必要的写锁，没有不必要的序列增量。也不需要强制转换。
如果写锁是用例中的一个特性，则可以在现有表达式中使用SELECT FOR UPDATE。
如果你需要知道是否插入了新行，你可以在顶级的UNION中添加一个标志列:
SELECT id, TRUE AS inserted FROM inserted
UNION ALL
SELECT id, FALSE FROM extant
我也遇到了同样的问题，我使用“做更新”而不是“什么都不做”来解决它，尽管我没有什么可更新的。在你的情况下，它会是这样的:
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;
这个查询将返回所有的行，无论它们是刚刚插入的还是之前已经存在的。
