PostgreSQL插入时按数组长度条件返回ID的问题求助

你遇到的query returned more than one row错误，是因为当my_array包含多个元素时，INSERT语句会生成多行插入记录，原代码的RETURNING子句会为每一行返回一个结果，而INTO my_variable只能接收单行值，从而触发报错。

核心思路是确保RETURNING最终只返回单行结果，同时满足「无论数组长度多少都执行插入，仅数组长度为1时返回ID」的需求。可以通过将插入结果聚合后再做条件判断来实现：

WITH x_array_items AS (
    SELECT id FROM unnest(my_array) AS id
), inserted AS (
    INSERT INTO "table" (name, description)
    SELECT name, description
    FROM other_table ot
    JOIN x_array_items xa ON ot.id = xa.id
    RETURNING id
)
SELECT CASE WHEN array_length(my_array, 1) = 1 THEN MAX(id) END
INTO my_variable;

代码说明 #

1. CTE部分：x_array_items负责拆分输入数组my_array，获取需要关联的ID列表。
2. 插入操作：inserted CTE执行实际的插入逻辑，同时返回所有插入行的ID。
3. 结果处理：外层SELECT通过MAX(id)聚合插入的ID（确保返回单行），再通过CASE判断：
   • 当my_array长度为1时，MAX(id)就是唯一插入行的ID，最终赋值给my_variable；
   • 当my_array长度大于1时，CASE返回null，my_variable最终得到null，同时插入操作已完整执行。

也可以用更简洁的写法，直接在RETURNING后聚合：

WITH x_array_items AS (
    SELECT id FROM unnest(my_array) AS id
)
INSERT INTO "table" (name, description)
SELECT name, description
FROM other_table ot
JOIN x_array_items xa ON ot.id = xa.id
RETURNING CASE WHEN array_length(my_array, 1) = 1 THEN MAX(id) OVER () END
LIMIT 1;

这里用MAX(id) OVER ()全局聚合所有插入的ID，再通过LIMIT 1确保只返回单行，效果和前一种方案一致。

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