sql - 如何在 PostgreSQL 中使用数组进行批量更新？

Question

在 PostgreSQL 数据库中，我有一个名为 services 的表，它有 4 列。是否可以通过数组进行批量更新？我试过这样的代码，但它引发了错误:

SQL Error [0A000]: ERROR: set-returning functions are not allowed in WHERE

SQL:

UPDATE SERVICES
SET
    NAME = UNNEST(ARRAY['NAME OF THE FIRST SERVICE', 'NAME OF THE SECOND SERVICE']),
    ACTIVE = UNNEST(ARRAY[FALSE, TRUE]),
    DESCRIPTION = UNNEST(ARRAY['DESCRIPTION OF THE FIRST SERVICE', 'DESCRIPTION OF THE SECOND SERVICE'])
WHERE
    ID = UNNEST(ARRAY['e1433cd2-7591-4a74-b910-33ea89d87ecd', '6c27c413-4be2-4a89-bea0-e713445ebfe1']);

Answer 1

您可以在子查询中取消嵌套它们:

UPDATE services s
    SET name = x.name,
        active = x.active,
        description = x.description
FROM (SELECT UNNEST(ARRAY['NAME OF THE FIRST SERVICE', 'NAME OF THE SECOND SERVICE']) as name,
             UNNEST(ARRAY[FALSE, TRUE]) as active,
             UNNEST(ARRAY['DESCRIPTION OF THE FIRST SERVICE', 'DESCRIPTION OF THE SECOND SERVICE']) as description
             UNNEST(ARRAY['e1433cd2-7591-4a74-b910-33ea89d87ecd', '6c27c413-4be2-4a89-bea0-e713445ebfe1']) as id
     ) x
WHERE s.id = x.id;

当单个 select 中有多个 unnest() 时，Postgres 会并行取消嵌套它们(而不是生成笛卡尔积)。行数是最大数组的行数，较短的数组用 null 填充。