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

在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']);

最佳答案

您可以在子查询中运行它们：

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;

当一个unnest()中有多个selects时，Postgres并行地运行它们（而不是生成笛卡尔积）。行数是最大数组的行数，较短的数组用nulls填充。