PostgreSQL同表取行改字段后插入唯一新行语法错误排查求助

• 语法错误：insert into id_array 语句用法错误，INSERT是用来往数据表写入数据的语法，不能用来给数组变量赋值，你需要用SELECT array_agg(entry) INTO id_array FROM table_1 WHERE affiliation = 52; 实现数组赋值
• 逻辑错误：判断重复的EXISTS查询没有加对应entry的过滤条件，只要表中存在任意一条affiliation=48的记录，所有新数据都会插入失败
• 语法错误：raise notice 后的提示字符串使用了双引号，PostgreSQL中字符串字面量必须使用单引号，双引号仅用于识别表名、字段名等标识符
• 语法错误：循环结束标记写错，endloop需要加空格写为end loop
• 逻辑冗余：整个场景不需要写循环逻辑，用PostgreSQL原生的INSERT ... SELECT语法可以更高效完成需求

首先先给entry和affiliation组合加唯一约束，从底层保证不会出现重复数据：

CREATE UNIQUE INDEX IF NOT EXISTS idx_table1_entry_affiliation ON table_1 (entry, affiliation);

然后直接用单条插入语句完成需求，自动跳过已存在的重复组合：

INSERT INTO table_1 (affiliation, entry)
SELECT 48, entry
FROM table_1
WHERE affiliation = 52
ON CONFLICT (entry, affiliation) DO NOTHING;

该语句会自动遍历所有affiliation=52的条目，批量插入affiliation=48的对应数据，自动跳过已存在的entry+affiliation重复组合，不需要额外写循环和判断逻辑，性能远高于自定义PL/pgSQL循环。

如果你确实需要用PL/pgSQL实现，修正后的代码如下：

do $$
declare
id_array int[];
current_entry int;
begin
-- 正确给数组赋值
SELECT array_agg(entry) INTO id_array FROM table_1 WHERE affiliation = 52;
-- 遍历数组
foreach current_entry in array id_array
loop
-- 增加entry过滤条件，判断当前entry+affiliation=48是否已存在
if not exists (select 1 from table_1 where affiliation = 48 and entry = current_entry)
then 
insert into table_1 (affiliation, entry) values (48, current_entry);
else
raise notice 'entry % already exists with affiliation 48', current_entry;
end if;
end loop;
end; $$

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