postgresql - 使用成对输入进行批量更新的最有效方法

Question

假设我想要进行批量更新，为 a 值的集合设置 a=b。这可以通过一系列 UPDATE 查询轻松完成:

UPDATE foo SET value='foo' WHERE id=1
UPDATE foo SET value='bar' WHERE id=2
UPDATE foo SET value='baz' WHERE id=3

但现在我想我想批量执行此操作。我有一个包含 ids 和新值的二维数组:

[ [ 1, 'foo' ]
  [ 2, 'bar' ]
  [ 3, 'baz' ] ]

有没有一种有效的方法可以在单个 SQL 查询中执行这三个 UPDATE？

我考虑过的一些解决方案:

1. 临时表

   CREATE TABLE temp ...;
   INSERT INTO temp (id,value) VALUES (....);
   UPDATE foo USING temp ...
   

   但这确实只是解决了问题。尽管执行批量插入可能更容易(或者至少不那么难看)，但仍然至少有三个查询。

2. 通过将数据对作为 SQL 数组传递来对输入进行非规范化。但这使得查询变得异常难看

   UPDATE foo
   USING (
       SELECT
           split_part(x,',',1)::INT AS id,
           split_part(x,',',2)::VARCHAR AS value
       FROM (
           SELECT UNNEST(ARRAY['1,foo','2,bar','3,baz']) AS x
       ) AS x;
   )
   SET value=x.value WHERE id=x.id
   

   这使得使用单个查询成为可能，但使该查询变得丑陋且效率低下(特别是对于混合和/或复杂的数据类型)。

有更好的解决办法吗？或者我应该诉诸多个 UPDATE 查询？

Answer 1

通常，您希望从具有足够索引的表进行批量更新以使合并变得容易:

CREATE TEMP TABLE updates_table
        ( id integer not null primary key
        , val varchar
        );
INSERT into updates_table(id, val) VALUES
 ( 1, 'foo' ) ,( 2, 'bar' ) ,( 3, 'baz' )
        ;

UPDATE target_table t
SET value = u.val
FROM updates_table u
WHERE t.id = u.id
        ;

因此，您可能应该通过以下内容填充 update_table:

<小时/>

INSERT into updates_table(id, val)
SELECT
        split_part(x,',',1)::INT AS id,
        split_part(x,',',2)::VARCHAR AS value
    FROM (
        SELECT UNNEST(ARRAY['1,foo','2,bar','3,baz']) 
         ) AS x
     ;

<小时/>

请记住:updates_table 中 id 字段的索引(或主键)非常重要。 (但是对于像这样的小集合，哈希连接可能会由优化器选择)

<小时/>

此外:对于更新，重要的是避免使用相同值进行更新，这会导致创建额外的行版本以及更新后产生的VACUUM事件已 promise :

UPDATE target_table t
    SET value = u.val
    FROM updates_table u
    WHERE t.id = u.id
    AND (t.value IS NULL OR t.value <> u.value)
            ;