PostgreSQL插入链报错‘column does not exist’问题排查

问题描述 #

我需要实现以下逻辑：针对get_contest_participants_candidates()返回的每一行数据，在contests表插入一条记录，同时在contestParticipants表插入两条关联该contest ID的记录。执行原始SQL时出现报错：

• 初始报错：column does not exist
• 修改select (first, second)为select first, second后报错：subquery must return only one column

原始SQL代码 #

begin
    with participants as(
      select (first, second)
      from get_contest_participants_candidates(competition_id_input)
    ), new_contest as(
      insert into contests (competition_id)
      select competition_id_input
      from participants
      returning id
    ) 
    insert into "contestParticipants" (contest_id, contestant_id)
    values(
      (select c.id, p.first from new_contest c, participants p),
      (select c.id, p.second from new_contest c, participants p)
    );
  end;

函数返回结果 #

预期插入结果 #

contests表

contestParticipants表

错误原因 #

1. 复合类型列导致列不存在：select (first, second)的写法会创建一个复合类型的单列（默认列名为row），而非单独的first和second列，后续引用p.first自然会提示列不存在。
2. 子查询返回多列不符合语法：values()中的每个值位置只能接受单个值，但你写的子查询返回了c.id和p.first两列，违反语法要求。
3. 关联逻辑混乱：原代码中new_contest和participants采用笛卡尔积关联，无法将新生成的contest ID与对应的first/second选手正确绑定，会导致数据错乱。

修正后的SQL代码 #

begin
  with numbered_participants as (
    -- 给每个选手对加行号，用于后续关联新生成的contest
    select 
      first, 
      second,
      row_number() over () as row_num
    from get_contest_participants_candidates(competition_id_input)
  ), new_contests as (
    -- 插入contest，同时返回生成的id和对应的行号
    insert into contests (competition_id)
    select competition_id_input
    from numbered_participants
    returning 
      id,
      row_number() over () as row_num
  )
  -- 通过行号关联，将每个contest对应的两个选手拆分为两行插入
  insert into "contestParticipants" (contest_id, contestant_id)
  select 
    nc.id, 
    unnest(array[np.first, np.second]) as contestant_id
  from new_contests nc
  join numbered_participants np on nc.row_num = np.row_num;
end;

修正说明 #

1. 去掉复合类型写法，直接查询first和second作为独立列，避免列不存在的报错。
2. 给选手对添加行号，插入contests时也生成对应行号，通过行号确保新生成的contest ID与原始选手对正确绑定。
3. 使用unnest(array[np.first, np.second])将每个contest对应的两个选手拆分为两行，一次性插入到contestParticipants表，符合语法要求且逻辑清晰。

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