postgresql 重写大型IN子句的最佳方法是什么？

2cmtqfgy 于 2023-05-17 发布在 PostgreSQL

关注(0)|答案(1)|浏览(274)

我使用go和gorm编写了一个API，它在我们的数据库上运行计算并返回结果。
我刚刚在使用聚合时达到了IN条件的参数限制。查询示例：

SELECT SUM(total_amount) from Table where user_id in(...70k parameters) group by user_id

我目前的一个边缘案例有> 65535个用户ID，所以我的Postgres客户端抛出了一个错误：

got 66037 parameters but PostgreSQL only supports 65535 parameters

我不知道该怎么做。它将处理这种边缘情况的大量参数，同时不会影响我的典型用例。我是否将id分块并遍历多个查询，将其存储在内存中，直到获得所需的所有数据？使用ANY(VALUES)...
很明显，从查询中我对Postgres的了解非常有限，所以任何帮助都会非常感激。

1条答案

您可以将user_id IN (value [, ...])替换为以下之一：

user_id IN (subquery)
user_id = ANY (subquery)
user_id = ANY (array expression)

子查询和数组都没有表现出同样的限制。最短的输入语法是：

user_id = ANY ('{1,2,3}'::int[])  -- make array type match type of user_id

详细信息和更多选项：

如何在WHERE子句中使用ANY而不是IN？
或者您可以创建一个（临时）表tmp_usr(user_id int)，导入到它，可能使用SQL COPY或psql \copy而不是INSERT，以便使用大集合获得最佳性能，然后 join 到表中，如下所示：

SELECT user_id, SUM(total_amount)
FROM   tbl
JOIN   tmp_usr USING (user_id)
GROUP  BY user_id;

顺便说一句，您需要将user_id包含在SELECT列表中以识别总和。