使用unnest在PostgreSQL中进行批量更新

问题描述：

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

I am trying to do bulk update:

> 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

In the last two rows enabled is false while I expected it to be true

为什么会这样，怎么会

谢谢。

答

您在打电话吗？ unnest 在 FROM 子句中出现3次，这意味着您正在执行 CROSS JOIN （笛卡尔积）的3。

You are calling unnest 3 times on FROM clause, that means you are doing a CROSS JOIN (cartesian product) of the 3.

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

If you are on PostgreSQL 9.4 or higher, you can simple do one call of unnest giving each array as input:

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 unnest / code>代替 FROM ：

Another option, for any version, is to add the call to unnest in SELECT instead of FROM:

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 ，您可能不需要。例如：

In both cases, but specially on the last one, you must be sure each array have the exact same number of elements. If it doesn't on the first method each missing row will be filled as NULL, but the second one it will return as many rows as the LCM of the number of rows returned by each, what you probably do not want. Example:

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)

在表函数调用以获取更多信息。

使用unnest在PostgreSQL中进行批量更新

相关推荐