如何更新所有字段而不提及它们?[副本]

这个 UPDATE syntax 要求 显式命名目标列。 避免这种情况的可能原因:

• 您有很多列,只是想缩短语法。
• 你没有 知道 列名,唯一列除外。

"All columns" 必须意味着 “目标表的所有列” (或至少 “表格的前几列” )按照匹配顺序和匹配数据类型。否则,无论如何,您都必须提供目标列名列表。

试验台:

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

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

1. DELETE & INSERT 改为单查询

不知道任何列名,除了 id .

仅适用于 “目标表的所有列” 虽然语法甚至适用于前导子集,但目标表中的多余列将被重置为其各自的列默认值(默认NULL) 删除 和 插入 .

扰乱市场( INSERT ... ON CONFLICT ... )需要避免并发写负载下的并发/锁定问题,这只是因为在Postgres中还没有通用的方法来锁定尚未存在的行( value locking ).

您的特殊要求只会影响 更新 部分。可能的并发症不适用于以下情况 现有的 行受到影响。这些都锁好了。再简化一些,你可以把你的案子减少到 删除 和 插入 :

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模型中 更新 大体上与 删除 和 插入 -除了一些并发性、触发器、HOT更新和大列值存储异常的极端情况,即“TOASTed”值。由于您无论如何都要替换所有行,因此只需在 插入 。在提交事务之前,已删除的行将保持锁定状态。这个 插入 如果并发事务碰巧并发插入以前不存在的键值,则可能只会发现它们的冲突行(在 删除 ,但在 插入 ).

在这种特殊情况下,您将丢失受影响行的其他列值。没有提出例外。但如果竞争查询具有相同的优先级,这几乎不是问题:另一个查询获胜 一些 排。此外,如果另一个查询是类似的UPSERT,则其替代方案是等待此事务提交,然后立即更新。“胜利”可能是一场代价高昂的胜利。

关于“空更新”:

• 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 :心烦意乱。冲突行 身份证件 没有改变,但也 已锁定 .
• 第三次CTE del 删除冲突行。他们仍然被锁着。
• 第四CTE ins 插入件 整排 仅允许进行同一笔交易
• 决赛 SELECT 是可选的,显示发生了什么。

使用以下命令检查空更新测试(之前和之后):

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

(已注释掉)检查行中是否有任何更改 AND t <> d 即使使用NULL值也能工作,因为我们正在比较两个类型化的行值 according to the manual:

两个NULL字段值被认为相等,NULL被认为大于非NULL

但所有列都必须支持 = / <> 用于行比较的运算符。请参阅:

• How to query a json column for empty objects?

2.动态SQL

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

诀窍是让Postgres根据系统目录中的列名动态构建查询字符串,然后执行它。

代码见相关答案:

• Update multiple columns in a trigger function in plpgsql

• Bulk update of all columns

• SQL update fields of one table from fields of another one

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

以下使用了以下代码段 one of his other answers 在我的案例中再现“return 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 ).