如何使用INSERT``ON CONFLICT''更新所有列？

Question

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

我正在使用PostgreSQL 9.5.5。

Answer 1

该UPDATE语法 要求
显式命名目标列。避免这种情况的可能原因：

• 您有很多列，只是想缩短语法。
• 除了唯一列，您不 知道 列名。

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

测试表：

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

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

1. DELETE＆INSERT在单个查询中

不知道除以外的任何列名称id。

仅适用于 “目标表的所有列” 。尽管语法甚至适用于前导子集，但目标表中多余的列将使用DELETE和重置为NULL INSERT。

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

您的特殊要求仅会影响UPDATE零件。可能的复杂性不适用于影响 现有
行的地方。那些被正确锁定。简化一些，您可以将案例减少为DELETE和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）。

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

关于“空更新”：

• 如何（或可以）在多列上选择DISTINCT？

不，我的查询必须赢！

好的，您要求它：

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;

如何？

• 第一CTEdata仅提供数据。可以是一张桌子。
• 第二CTE ups：UPPERT。冲突的行id不会更改，但也会 锁定 。
• 第三CTEdel删除冲突的行。他们保持锁定状态。
• 第四CTEins插入 整行 。只允许同一笔交易
• 最终的SELECT仅用于演示以显示发生了什么。

要检查空更新测试（之前和之后），请执行以下操作：

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

（注释掉）检查行中的任何更改都AND t <>d可以使用NULL值，因为我们正在根据手册比较两个类型化的行值：

两个NULL字段值被视为相等，并且NULL被认为大于非NULL

2.动态SQL

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

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