单条SQL使用多个CTE批量插入企业数据出现语法错误如何解决？

错误原因 #

你的写法存在3个核心问题：

1. 语法不符合规范：给整组CTE外层添加了多余括号，尝试将独立CTE组嵌套为子表达式，违反PostgreSQL CTE语法要求
2. 滥用RECURSIVE关键字：递归CTE仅用于处理树形结构、迭代计算类场景，你的插入场景完全不需要该关键字
3. 命名冲突：同一WITH语句块内不允许定义重名的CTE，你重复定义newCompany、newProfile必然触发报错

最优解决方案 #

直接把所有待插入的企业信息放到一个基础CTE中批量声明，后续关联插入逻辑复用一套即可，代码简洁且执行效率更高：

WITH all_companies AS (
    -- 新增企业只需在下方VALUES列表追加对应行即可
    VALUES 
        (1, 'CompanyA', 'https://storage.googleapis.com/xxxx/assets/xxxx/header.jpg'),
        (2, 'CompanyB', 'https://storage.googleapis.com/xxxx/assets/xxxx/header.jpg')
    AS t(company_id, company_name, header_img_url)
),
newCompany AS (
    INSERT INTO company (company_id, company_name)
    SELECT company_id, company_name FROM all_companies
    RETURNING company_id, company_name
),
newProfile AS (
    INSERT INTO companyProfile (company_id, profile_header_image_url)
    SELECT ac.company_id, ac.header_img_url
    FROM all_companies ac
    JOIN newCompany nc ON ac.company_id = nc.company_id
    RETURNING company_id, profile_id, profile_header_image_url
),
newCompanyProfile AS (
    INSERT INTO company_profile (company_id, profile_id)
    SELECT nc.company_id, np.profile_id 
    FROM newCompany nc
    JOIN newProfile np ON nc.company_id = np.company_id
)
SELECT
    nc.company_id,
    nc.company_name,
    np.profile_id,
    np.profile_header_image_url
FROM newCompany nc
JOIN newProfile np ON nc.company_id = np.company_id;

如果所有企业的头像地址完全一致，也可以把固定地址直接写在插入语句里，不用放到all_companies的VALUES列表中。

特殊场景兼容（每家企业逻辑差异大） #

如果不同企业的插入逻辑差异非常大，确实需要独立编写每组插入逻辑，只需给不同企业的CTE加唯一后缀避免重名，最后用UNION ALL合并返回结果即可：

WITH 
-- 企业A插入逻辑
newCompany1 AS (
    INSERT INTO company (company_id,company_name)
    VALUES(1, 'CompanyA')
    RETURNING company_id, company_name
),
newProfile1 AS (
    INSERT INTO companyProfile (company_id, profile_header_image_url)
    SELECT company_id, 'https://storage.googleapis.com/xxxx/assets/xxxx/header.jpg' FROM newCompany1
    RETURNING company_id, profile_id, profile_header_image_url
),
newCompanyProfile1 AS (
    INSERT INTO company_profile (company_id, profile_id)
    SELECT c.company_id, p.profile_id FROM newCompany1 c JOIN newProfile1 p ON c.company_id = p.company_id
),
-- 企业B插入逻辑
newCompany2 AS (
    INSERT INTO company (company_id,company_name)
    VALUES(2, 'CompanyB')
    RETURNING company_id, company_name
),
newProfile2 AS (
    INSERT INTO companyProfile (company_id, profile_header_image_url)
    SELECT company_id, 'https://storage.googleapis.com/xxxx/assets/xxxx/header.jpg' FROM newCompany2
    RETURNING company_id, profile_id, profile_header_image_url
),
newCompanyProfile2 AS (
    INSERT INTO company_profile (company_id, profile_id)
    SELECT c.company_id, p.profile_id FROM newCompany2 c JOIN newProfile2 p ON c.company_id = p.company_id
)
-- 合并返回所有企业结果
SELECT company_id, company_name, profile_id, profile_header_image_url
FROM newCompany1 c JOIN newProfile1 p ON c.company_id = p.company_id
UNION ALL
SELECT company_id, company_name, profile_id, profile_header_image_url
FROM newCompany2 c JOIN newProfile2 p ON c.company_id = p.company_id;

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