如何使用INSERT ... ON CONFLICT ...更新所有列？ [英] How to update all columns with INSERT ... ON CONFLICT ...?

问题描述

我有一个带有单个主键的表。当我尝试执行插入操作时，尝试插入具有现有键的行可能会导致冲突。我要允许插入更新所有列吗？有什么简单的语法吗？我试图让它更新所有列。

I have a table with a single primary key. When I attempt to do an insert there may be a conflict caused by trying to insert a row with an existing key. I want to allow the insert to update all columns? Is there any easy syntax for this? I am trying to let it "upsert" all columns.

我正在使用PostgreSQL 9.5.5。

I am using PostgreSQL 9.5.5.

推荐答案

UPDATE 语法 需要以明确命名目标列。
避免这种情况的可能原因：

The UPDATE syntax requires to explicitly name target columns. Possible reasons to avoid that:

• 您有很多列，只是想缩短语法。


• 除唯一列外，您不知道列名。

• You have many columns and just want to shorten the syntax.
• You do not know column names except for the unique column(s).

所有列 必须表示目标表的所有列 （或至少匹配顺序和匹配数据类型的表前导列 ）。否则，您仍然必须提供目标列名称的列表。

"All columns" has to mean "all columns of the target table" (or at least "leading columns of the table") in matching order and matching data type. Else you'd have to provide a list of target column names anyway.

测试表：

CREATE TABLE tbl (
   id    int PRIMARY KEY
 , text  text
 , extra text
);

INSERT INTO tbl AS t
VALUES (1, 'foo')
     , (2, 'bar');

1。 删除&在单个查询中改为 INSERT

不知道除 id 。

1. DELETE & INSERT in single query instead

Without knowing any column names except id.

仅适用于目标表的所有列 。尽管语法甚至适用于前导子集，但目标表中多余的列将通过 DELETE 和 INSERT 。

Only works for "all columns of the target table". While the syntax even works for a leading subset, excess columns in the target table would be reset to NULL with DELETE and INSERT.

UPSERT（ INSERT ... ON CONFLICT ... ）可以避免并发/锁定问题在并发写入负载下，并且仅是因为没有通用的方法来锁定Postgres中尚未存在的行（ 值锁定 ）。

UPSERT (INSERT ... ON CONFLICT ...) is needed to avoid concurrency / locking issues under concurrent write load, and only because there is no general way to lock not-yet-existing rows in Postgres (value locking).

您的特殊要求仅会影响 UPDATE 部分。在现有行受到影响的地方，可能不会出现复杂的情况。那些被正确锁定。简化一些操作，可以将大小写减少为 Delete 和 INSERT ：

Your special requirement only affects the UPDATE part. Possible complications do not apply where existing rows are affected. Those are locked properly. Simplifying some more, you can reduce your case to DELETE and INSERT:

WITH data(id) AS (              -- Only 1st column gets explicit name!
   VALUES
      (1, 'foo_upd', 'a')       -- changed
    , (2, 'bar', 'b')           -- unchanged
    , (3, 'baz', 'c')           -- new
   )
, del AS (
   DELETE FROM tbl AS t
   USING  data d
   WHERE  t.id = d.id
   -- AND    t <> d              -- optional, to avoid empty updates
   )                             -- only works for complete rows
INSERT INTO tbl AS t
TABLE  data                      -- short for: SELECT * FROM data
ON     CONFLICT (id) DO NOTHING
RETURNING t.id;

在Postgres MVCC模型中， UPDATE 基本上与 DELETE 和 INSERT 相同（除了一些并发，HOT更新和大列的极端情况之外）值存储在行外）。由于您仍然想替换所有行，因此只需删除 INSERT 之前的冲突行。删除的行将保持锁定，直到提交事务。 INSERT 如果并发事务恰巧同时插入它们（在 DELETE ，但在 INSERT 之前。）

In the Postgres MVCC model, an UPDATE is largely the same as DELETE and INSERT anyway (except for some corner cases with concurrency, HOT updates, and big column values stored out of line). Since you want to replace all rows anyway, just remove conflicting rows before the INSERT. Deleted rows remain locked until the transaction is committed. The INSERT might only find conflicting rows for previously non-existing key values if a concurrent transaction happens to insert them concurrently (after the DELETE, but before the INSERT).

在此特殊情况下，您将丢失受影响的行的其他列值案件。没有异常。但是，如果竞争查询具有相同的优先级，那么这几乎不是问题：另一个查询赢得了 some 行。同样，如果另一个查询是类似的UPSERT，则它的替代方法是等待该事务提交，然后立即进行更新。 获胜可能是一次胜利。

You would lose additional column values for affected rows in this special case. No exception raised. But if competing queries have equal priority, that's hardly a problem: the other query won for some rows. Also, if the other query is a similar UPSERT, its alternative is to wait for this transaction to commit and then updates right away. "Winning" could be a Pyrrhic victory.

关于空更新：

• 我如何（或者我可以在多个列上选择DISTINCT吗？

• How do I (or can I) SELECT DISTINCT on multiple columns?

好，您要求输入：

WITH data(id) AS (                   -- Only 1st column gets explicit name!
   VALUES                            -- rest gets default names "column2", etc.
   (1, 'foo_upd', NULL)              -- changed
 , (2, 'bar', NULL)                  -- unchanged
 , (3, 'baz', NULL)                  -- new
 , (4, 'baz', NULL)                  -- new
   )
 , ups AS (
   INSERT INTO tbl AS t
   TABLE  data                       -- short for: SELECT * FROM data
   ON     CONFLICT (id) DO UPDATE
   SET    id = t.id
   WHERE  false                      -- never executed, but locks the row!
   RETURNING t.id
   )
 , del AS (
   DELETE FROM tbl AS t
   USING  data     d
   LEFT   JOIN ups u USING (id)
   WHERE  u.id IS NULL               -- not inserted !
   AND    t.id = d.id
   -- AND    t <> d                  -- avoid empty updates - only for full rows
   RETURNING t.id
   )
 , ins AS (
   INSERT INTO tbl AS t
   SELECT *
   FROM   data
   JOIN   del USING (id)             -- conflict impossible!
   RETURNING id
   )
SELECT ARRAY(TABLE ups) AS inserted  -- with UPSERT
     , ARRAY(TABLE ins) AS updated   -- with DELETE & INSERT;

如何？

• 第一CTE data 仅提供数据。


• 第二CTE ups ：UPPERT。
id 有冲突的行不会更改，但也会锁定。

• 第三CTE del 删除冲突的行。它们保持锁定状态。


• 第四CTE ins 插入整行。仅允许相同的交易


• 最终的SELECT仅用于演示以显示发生了什么情况。

• The 1st CTE data just provides data. Could be a table instead.
• The 2nd CTE ups: UPSERT. Rows with conflicting id are not changed, but also locked.
• The 3rd CTE del deletes conflicting rows. They remain locked.
• The 4th CTE ins inserts whole rows. Only allowed for the same transaction
• The final SELECT is only for the demo to show what happened.

使用以下命令检查空更新测试（之前和之后）：

To check for empty updates test (before and after) with:

SELECT ctid, * FROM tbl; -- did the ctid change?

2。动态SQL

这也适用于前导列的子集，保留现有值。

2. Dynamic SQL

This works for a subset of leading columns too, preserving existing values.

窍门是让Postgres动态地使用系统目录中的列名构建查询字符串，然后执行它。

The trick is to let Postgres build the query string with column names from the system catalogs dynamically, and then execute it.

查看相关的代码答案：

• 在plpgsql中的触发器函数中更新多列

批量更新所有列

SQL更新字段中一个表的字段另一个

这篇关于如何使用INSERT ... ON CONFLICT ...更新所有列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！