PostgreSQL中使用CTE拆分course_id多ID数据问题求助

错误原因分析 #

你之前的代码存在两个核心问题：

1. 普通SELECT生成的CTE是只读的，PostgreSQL不允许对这类CTE执行UPDATE操作，因此会抛出"relation 'gia_su_pro_virtual' does not exist"的错误。
2. 你的思路是替换分隔符，但这无法实现"拆分ID生成新记录"的需求，方向有误。

正确实现方式 #

要在不修改原表的前提下，拆分多课程ID并生成独立记录，可使用PostgreSQL的string_to_array+unnest组合函数：

基础版查询 #

SELECT 
    gsp.id, -- 保留原表的主键/唯一标识字段
    gsp.other_column, -- 替换为你需要保留的其他字段
    unnest(string_to_array(gsp.course_id, E'\u00B6')) AS single_course_id
FROM gia_su_pro gsp;

优化版（处理空值和空格） #

如果存在拆分后为空的ID或前后带空格的情况，可添加过滤和修剪：

SELECT 
    gsp.id,
    gsp.other_column,
    trim(single_course_id) AS single_course_id
FROM gia_su_pro gsp,
     unnest(string_to_array(gsp.course_id, E'\u00B6')) AS single_course_id
WHERE trim(single_course_id) <> '';

复用方案（创建视图） #

如果需要频繁查询拆分后的结果，可以创建视图（不占用物理存储，每次查询动态计算）：

CREATE VIEW gia_su_pro_courses AS
SELECT 
    gsp.id,
    gsp.other_column,
    trim(unnest(string_to_array(gsp.course_id, E'\u00B6'))) AS single_course_id
FROM gia_su_pro gsp
WHERE trim(unnest(string_to_array(gsp.course_id, E'\u00B6'))) <> '';

代码说明 #

• string_to_array(gsp.course_id, E'\u00B6')：将course_id字段按段落符（Pilcrow符号，Unicode编码\u00B6）拆分为数组，每个元素对应一个课程ID。
• unnest(...)：将数组中的每个元素展开为单独的数据行，实现"一个多ID记录拆分为多条单ID记录"的效果。

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