如何向PostgreSQL查询传递数组参数并完成批量更新？

你的问题核心是要按数组位置一一对应更新表中指定行，之前的循环写法错误在于每次都会更新所有符合ANY条件的行，最终只剩最后一个值生效。正确的做法是将三个输入数组转换为按位置关联的临时行集，再与原表关联更新。

推荐SQL写法 #

使用UPDATE ... FROM结合unnest()和WITH ORDINALITY来保证数组元素的位置对应：

UPDATE bookIds b
SET checked_out_version = t.new_version
FROM (
  SELECT 
    b_arr.book_id,
    l_arr.location_id,
    v_arr.new_version
  FROM unnest(:bookIDS::integer[]) WITH ORDINALITY AS b_arr(book_id, rn)
  JOIN unnest(:locationIDS::bigint[]) WITH ORDINALITY AS l_arr(location_id, rn) USING(rn)
  JOIN unnest(:checkedOutVersions::integer[]) WITH ORDINALITY AS v_arr(new_version, rn) USING(rn)
) t
WHERE b.book_id = t.book_id
  AND b.location_id = t.location_id;

原理说明 #

1. unnest()函数将每个输入数组拆分为多行，每行对应数组中的一个元素
2. WITH ORDINALITY会为每个拆分出的元素添加一个位置序号（rn），确保三个数组中同一位置的元素能被正确关联
3. 子查询t会生成一组组对应的book_id、location_id和new_version数据
4. 最后通过原表的book_id和location_id与子查询关联，实现精准的对应更新

注意事项 #

• 必须保证传入的三个数组长度完全一致，否则会出现元素匹配错位或部分数据无法更新的问题
• Java端传递参数时，直接使用对应类型的数组即可（如int[]对应PostgreSQL的integer[]，long[]对应bigint[]），多数JDBC驱动（如PostgreSQL JDBC Driver）都支持直接传递数组参数
• 避免使用循环更新的写法，不仅效率低下，还会导致不符合预期的批量覆盖

示例验证 #

当传入以下数组：

• bookIds: [12,15]
• LocationIDS: [1,1]
• checkedOutVersions: [11,20]

子查询t会生成两行数据：

关联原表更新后，即可得到你期望的结果。

内容的提问来源于stack exchange，提问作者Mjay92