【译】用于批量操作的 Postgres UNNEST 备忘单

原文作者 福布斯林德赛

原文链接https://dzone.com/articles/postgres-unnest-cheat-sheet-for-bulk-operations

如果您想一次交互数千行，UNNEST 是使 Postgres 查询快速可靠的唯一方法。

Postgres 通常非常快，但如果查询中的参数过多，它可能会变慢（甚至完全失败）。在批量处理数据时，这UNNEST是实现快速、可靠查询的唯一方法。UNNEST这篇文章有用于执行所有类型的批量交易的示例。

本文中的所有示例都假定数据库模式如下所示：

CREATE TABLE users (
  email TEXT NOT NULL PRIMARY KEY,
  favorite_color TEXT NOT NULL
)

一次插入数千条记录

要将多条记录一次性插入 Postgres 表中，最有效的方法是将每一列提供为单独的数组，然后用于UNNEST构造要插入的行。

您可以运行以下查询：

INSERT INTO users (email, favorite_color)
SELECT
  UNNEST(?::TEXT[]),
  UNNEST(?::TEXT[])

使用以下参数：

[
  ["joe@example.com", "ben@example.com", "mary@example.com"],
  ["red", "green", "indigo"]
]

请注意，无论您要插入多少行，您都只传递了 2 个参数。无论您要插入多少行，您都在使用相同的查询文本。这就是使查询如此高效的原因。

结果表如下所示：

在单个查询中将多条记录更新为不同的值

最强大的用例之一UNNEST是在单个查询中更新多条记录。UPDATE如果您想将它们全部设置为相同的值，则普通语句仅允许您一次更新多个记录，但这种方法更加灵活。

您可以运行以下查询：

UPDATE users
SET
  favorite_color=bulk_query.updated_favorite_color
FROM
  (
    SELECT
      UNNEST(?::TEXT[])
        AS email,
      UNNEST(?::TEXT[])
        AS updated_favorite_color
  ) AS bulk_query
WHERE
  users.email=bulk_query.email

使用以下参数：

[
  ["joe@example.com", "ben@example.com", "mary@example.com"],
  ["purple", "violet", "orange"]
]

结果表将如下所示：

这不仅让您可以在一个语句中更新所有这些记录，而且参数的数量现在固定为 2，无论您要更新多少行。

一次选择数千种不同的条件

你总是可以通过组合ORand来构建一个非常大的查询AND，但最终，如果你有足够的参数，这可能会开始变得很慢。

您可以运行以下查询：

SELECT * FROM users
WHERE (email, favorite_color) IN (
  SELECT
    UNNEST(?::TEXT[]),
    UNNEST(?::TEXT[])
)

使用以下参数：

[
  ["joe@example.com", "ben@example.com", "mary@example.com"],
  ["purple", "violet", "orange"]
]

它相当于运行：

SELECT * FROM users
WHERE
  (email='joe@example.com' AND favorite_color='purple')
  OR (email='ben@example.com' AND favorite_color='violet')
  OR (email='mary@example.com' AND favorite_color='orange')

在这里使用UNNEST可以让我们保持查询不变，并且只使用 2 个参数，无论我们要添加多少条件。

如果您需要更多控制，另一种方法是使用 INNER JOIN 而不是IN查询的一部分。例如，如果您需要测试不区分大小写，您可以这样做：

SELECT users.* FROM users
INNER JOIN (
  SELECT
    UNNEST(?::TEXT[]) AS email,
    UNNEST(?::TEXT[]) AS favorite_color
) AS unnest_query
ON (LOWER(users.email) = LOWER(unnest_query.email) AND LOWER(user.favorite_color) = LOWER(unnest_query.favorite_color))

一次性删除数千种不同的条件

就像SELECT，DELETE如果条件的复杂性变得过于极端，查询可能会变慢。

您可以运行以下查询：

DELETE FROM users
WHERE (email, favorite_color) IN (
  SELECT
    UNNEST(?::TEXT[]),
    UNNEST(?::TEXT[])
)

使用以下参数：

[
  ["joe@example.com", "ben@example.com", "mary@example.com"],
  ["purple", "violet", "orange"]
]

它相当于运行：

DELETE FROM users
WHERE
  (email='joe@example.com' AND favorite_color='purple')
  OR (email='ben@example.com' AND favorite_color='violet')
  OR (email='mary@example.com' AND favorite_color='orange')

就像 with 一样SELECT，在这里使用UNNEST可以让我们保持查询不变，并且只使用 2 个参数，而不管我们要添加多少条件。

如果您使用的是 node.js，则可以执行所有这些操作，而无需使用@database/pg-typedor来记住语法@database/pg-bulk。