PostgreSQL使用WITH子句执行INSERT SELECT报42P01错误如何解决

• INSERT语句的RETURNING子句仅支持引用当前插入目标表的字段、插入时生成的新值，无法访问INSERT ... SELECT语法中的源表字段。你的语句中RETURNING m.id AS meal_uuid尝试访问源表m的字段，超出了RETURNING的作用域，因此抛出找不到表m的报错。
• 额外的逻辑问题：你插入meal表时用uuid_generate_v4()生成了新的主键ID，返回旧meal表的ID也不符合后续关联新插入记录的需求。

场景1：不需要复制原有dog_diet的关联记录，直接生成对应记录 #

如果只需要给每个新插入的meal生成一条dog_diet记录，不需要关联原有dog_diet的数据，可直接修改RETURNING的返回字段即可：

BEGIN;

WITH dog_tmp AS (SELECT id FROM dog WHERE toy_id = '12345'),
meal_tmp as (INSERT INTO meal (id, dog_id, date_created, type)
    SELECT public.uuid_generate_v4(), dt.id, m.date_created, m.type
    FROM meal AS m
    JOIN dog_tmp dt ON dt.id = m.dog_id 
    -- 返回新生成的meal主键ID即可
    RETURNING id AS meal_uuid, date_created)

INSERT INTO dog_diet (id, meal_id, date_created)
SELECT public.uuid_generate_v4(), mt.meal_uuid, mt.date_created
FROM meal_tmp mt;

COMMIT;

场景2：需要复制原有meal对应的dog_diet关联记录 #

如果需要把原meal对应的所有dog_diet记录都复制到新meal下，需要提前生成新旧meal的ID映射关系，再执行后续插入：

BEGIN;

WITH dog_tmp AS (SELECT id FROM dog WHERE toy_id = '12345'),
-- 预生成新旧meal ID的映射，保留原meal的所有字段
meal_map AS (
    SELECT 
        public.uuid_generate_v4() AS new_meal_id,
        m.id AS old_meal_id,
        dt.id AS dog_id,
        m.date_created,
        m.type
    FROM meal m
    JOIN dog_tmp dt ON dt.id = m.dog_id
),
-- 插入新的meal记录
meal_inserted AS (
    INSERT INTO meal (id, dog_id, date_created, type)
    SELECT new_meal_id, dog_id, date_created, type
    FROM meal_map
    RETURNING id AS new_meal_id
)
-- 关联映射表拿到旧meal ID，批量复制对应的dog_diet记录
INSERT INTO dog_diet (id, meal_id, date_created)
SELECT 
    public.uuid_generate_v4(),
    mi.new_meal_id,
    dd.date_created
FROM meal_inserted mi
JOIN meal_map mm ON mm.new_meal_id = mi.new_meal_id
-- 如果dog_diet关联旧meal的字段是thread_id，可修改为dd.thread_id = mm.old_meal_id
JOIN dog_diet dd ON dd.meal_id = mm.old_meal_id;

COMMIT;

以上方案所有操作都在事务中执行，保证数据一致性，不会出现部分插入成功部分失败的问题。

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