如何使用INSERT更新所有列。。。关于冲突。。。？

Question

我有一个只有一个主键的表。当我试图插入时，可能会因为试图用现有键插入一行而引起冲突。我想允许插入更新所有列？有什么简单的语法吗？我试图让它“向上插入”所有列。

我使用PostgreSQL 9.5.5。

Answer 1

当id列不是第一列时，Erwin Brandstetter的回答似乎失败了。

在我的例子中，下面使用了他另一个答案中的一个片段来重现“返回ins/ups”功能：

DO
$do$
BEGIN
EXECUTE (
SELECT
'DROP TABLE IF EXISTS res_tbl; CREATE TABLE res_tbl AS
 WITH ins AS (
       INSERT INTO dest
       TABLE  src                      -- short for: SELECT * FROM data
       ON     CONFLICT (id) DO UPDATE
       SET    id = dest.id
       WHERE  false                    -- never executed, but locks the row!
       RETURNING id
    ),
    repl AS (
        UPDATE dest
        SET   (' || string_agg(          quote_ident(column_name), ',') || ')
            = (' || string_agg('src.' || quote_ident(column_name), ',') || ')
        FROM   src
        WHERE  src.id = dest.id
        AND    src <> dest             -- avoids empty updates ¹
        RETURNING dest.id
    )
 SELECT ARRAY(TABLE ins)  AS inserted  -- with UPSERT
      , ARRAY(TABLE repl) AS updated   -- with DYNAMIC UPDATE
;'
FROM   information_schema.columns
WHERE  table_name   = 'src'     -- table name, case sensitive
AND    table_schema = 'public'  -- schema name, case sensitive
AND    column_name <> 'id'      -- all columns except id)
);
END
$do$;

仅适用于所有列都可比较的整行更新（例如jsonb而不是json）。

Answer 2

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

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

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

测试表：

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

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

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

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

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

您的特殊要求只影响更新部分。在现有行受到影响的情况下，可能出现的复杂情况不适用。那些锁好了。通过进一步简化，您可以将案例简化为删除和插入：

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模型中，更新基本上与删除和插入相同（除了一些并发、热更新和大列值存储不一致的极端情况）。由于您仍想替换所有行，只需在插入之前删除冲突行即可。在提交事务之前，已删除的行将保持锁定状态。如果并发事务碰巧同时插入了以前不存在的键值，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只提供数据。可能是一个表。
• 第二个CTEup：UPSERT。具有冲突id的行不会被更改，但也会被锁定。
• 第三个CTEdel删除冲突行。它们保持锁定状态。
• 第4个CTEins插入整行。仅允许同一事务
• 最终的SELECT只是为了演示发生了什么。

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

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

（注释掉）检查行和t中是否有任何更改

两个空字段值被视为相等，空字段值被视为大于非空字段值

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

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

代码见相关答案：

>

所有列的批量更新

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