试图用CASE语句更新多行时，Postgres挂起在大表上

Question

我正在尝试使用postgres更新多行，我正在使用以下代码：

UPDATE foobar SET column_a = CASE
  WHEN column_b = '123' THEN 1
  WHEN column_b = '345' THEN 2
END;

如果我创建一个新表，它可以完美地工作，但是当在具有800万行的大表上运行它时，它会无限期地挂起。我首先在管理员（Web界面）和控制台中尝试。

然而，这工作得很好:

UPDATE foobar SET column_a=1 WHERE column_b='123';

我不太愿意在我的代码中实现这种方法，因为我一次会有数千个更新，并且更愿意将它们放在一个语句中。关于为什么第一个例子会挂postgres，而第二个例子会很好地工作，有什么想法吗？我只是仔细检查了一下，我没有在桌子上应用任何规则。

Answer 1

我保留这个问题，以防有人遇到这个问题。

这个查询是罪魁祸首：

UPDATE foobar SET column_a = CASE
  WHEN column_b = '123' THEN 1
  WHEN column_b = '345' THEN 2
END;

问题是它缺少 WHERE 语句，因此它尝试更新所有行。对于大型数据库，这可能是一个问题，在我的情况下，它只是超时了。一旦我在那里添加了where语句，它就解决了这个问题。

解决办法是：

UPDATE foobar SET column_a = CASE
   WHEN column_b = '123' THEN 1
   WHEN column_b = '345' THEN 2
END
WHERE column_b IN ('123','345')

Answer 2

声明：

CASE
  WHEN column_b = '123' THEN 1
  WHEN column_b = '345' THEN 2
END;

…只是简称：

CASE
  WHEN column_b = '123' THEN 1
  WHEN column_b = '345' THEN 2
  ELSE NULL
END

这意味着，如果没有 WHERE 子句，您的 UPDATE 语句不仅仅是“尝试”，它实际上会更新表中的每一行，其中大部分都为 NULL。
也许，列上的非空约束可以防止数据丢失...

我将同时获得数千个更新，并且更愿意将它们放在一个语句中。

对于大型集合，速度更快（更短）：

UPDATE foobar f
SET    column_a = val.a
FROM  (
   VALUES
     (123, 1)
    ,(345, 2)
   ) val(b, a)
WHERE f.column_b = val.b

加入一个集合，就可以轻松地遍历每一行的CASE分支的长列表。随着名单的增加，这种差异将迅速扩大。

此外，请确保在column_b上有索引。

您可以将VALUES表达式替换为任何产生适当行的表、视图或子选择。

注意:< br >我假设< code>column_a和< code>column_b的类型为< code>integer。在这种情况下，您问题中的< code>'123'前后的单引号没有任何帮助。你最好使用一个数字文字而不是字符串文字。(尽管它也适用于字符串文字。)

'123'之类的字符串文字默认为未知类型。
123之类的数字文字默认为integer-或bigint/数字如果数字太大。

如果您正在处理非默认数据类型，则必须显式转换。看起来像：

...
FROM  (
   VALUES
     ('123'::sometype, '1'::sometype)  -- first row defines row type
    ,('345', '2')
   ) val(b, a)
...