postgresql - 使用 unnest 在 postgreSQL 中批量更新

Question

我正在尝试进行批量更新:

> update ti_table set enabled=T.enabled 
    from (select * from 
         unnest(array['2001622', '2001624', '2007903']) as id,
         unnest(array[15,14,8]) as ver,
         unnest(array['type1', 'type1', 'type1']) as type,
         unnest(array[false, true, true]) as enabled) T 
    where ti_table.id=T.id AND ti_table.ver=T.ver AND ti_table.type=T.type;

然而，当我回读时:

> select id, ver, type, enabled from ti_table where id in ('2001622', '2001624', '2007903');

我明白了:

   id    | ver | type  | enabled 
---------+-----+-------+---------
 2001622 |  15 | type1 | f
 2001624 |  14 | type1 | f
 2007903 |   8 | type1 | f

在最后两行中，enabled 是 false 而我希望它是 true

为什么会发生这种情况，我该如何正确处理？

谢谢。

Answer 1

您在 FROM 子句上调用了 3 次 unnest，这意味着您正在执行 3 次的 CROSS JOIN(笛卡尔积)。

如果您使用的是 PostgreSQL 9.4 或更高版本，您可以简单地执行一次 unnest 调用，将每个数组作为输入:

select * from 
         unnest(
            array['2001622', '2001624', '2007903'],
             array[15,14,8],
             array['type1', 'type1', 'type1'],
             array[false, true, true]
        ) as u(id, ver, type, enabled)

对于任何版本，另一个选项是在 SELECT 而不是 FROM 中添加对 unnest 的调用:

select
   unnest(array['2001622', '2001624', '2007903']) as id,
   unnest(array[15,14,8]) as ver,
   unnest(array['type1', 'type1', 'type1']) as type,
   unnest(array[false, true, true]) as enabled

在这两种情况下，但特别是在最后一种情况下，您必须确保每个数组都具有完全相同数量的元素。如果它不在第一个方法中，每个缺失的行将被填充为 NULL，但第二个方法将返回与 LCM 一样多的行。每个返回的行数，你可能不想要的。示例:

SELECT * FROM unnest(array[1,2,3,4], array['a','b','c','d','e','f']);
 unnest | unnest 
--------+--------
      1 | a
      2 | b
      3 | c
      4 | d
 [null] | e
 [null] | f
(6 rows)

SELECT unnest(array[1,2,3,4]), unnest(array['a','b','c','d','e','f']);
 unnest | unnest 
--------+--------
      1 | a
      2 | b
      3 | c
      4 | d
      1 | e
      2 | f
      3 | a
      4 | b
      1 | c
      2 | d
      3 | e
      4 | f
(12 rows)

查看 table functions calls 上的文档获取更多信息。