如何将查询转换为CTE？CTE插入冲突时返回ID的实现疑问

一、普通查询转CTE的方法 #

CTE（公共表表达式）本质是把一段查询逻辑封装成临时数据集，转换方式非常直接：

1. 用WITH <CTE名称> AS (...)包裹原查询，括号内就是你原本的普通查询语句
2. 后续主查询可以直接像操作表一样引用这个CTE名称

举个例子：
普通查询：

SELECT id, label FROM fiche WHERE label LIKE 'test%';

转换为CTE：

WITH filtered_fiche AS (
    SELECT id, label FROM fiche WHERE label LIKE 'test%'
)
SELECT * FROM filtered_fiche WHERE id > 10;

如果需要多个CTE，用逗号分隔即可：

WITH filtered_fiche AS (
    SELECT id, label FROM fiche WHERE label LIKE 'test%'
),
counted AS (
    SELECT COUNT(*) AS total FROM filtered_fiche
)
SELECT * FROM filtered_fiche, counted;

二、CTE的工作原理 #

CTE是绑定当前SQL语句的临时数据集，只在当前语句执行周期内有效：

• 执行顺序：数据库会先执行WITH子句里的所有查询，生成临时结果集，再执行WITH之后的主查询
• 可读性优势：把复杂的嵌套查询拆分成多个逻辑独立的块，更容易维护
• 性能特性：大部分数据库（比如PostgreSQL）默认对CTE采用惰性求值，只有主查询用到时才会计算；部分场景下可以用MATERIALIZED关键字强制生成物化视图，避免重复计算
• 和临时表的区别：CTE不需要显式创建/销毁，只属于当前SQL语句，轻量化且不会污染数据库临时对象空间

三、解决INSERT冲突时返回对应ID的问题 #

你当前的代码逻辑存在错误：当插入成功时，inserted有数据，WHERE NOT EXISTS (...)会过滤掉所有结果；当冲突时，inserted为空，条件成立但子查询无数据，最终还是返回空。

以下是两种可行的解决方案：

方案1：插入失败时查询原表 #

先尝试插入并返回成功的记录，若没有插入结果（冲突），则直接从原表查询对应数据：

WITH inserted AS (
    INSERT INTO fiche(label) 
    VALUES ('label') 
    ON CONFLICT (label) DO NOTHING
    RETURNING id, label
)
SELECT id, label FROM inserted
UNION ALL
SELECT id, label FROM fiche WHERE label = 'label'
WHERE NOT EXISTS (SELECT 1 FROM inserted);

方案2：利用空更新返回冲突行 #

通过ON CONFLICT DO UPDATE执行一个无意义的更新操作（不改变数据），这样无论插入成功还是冲突，都会返回对应的记录：

INSERT INTO fiche(label) 
VALUES ('label') 
ON CONFLICT (label) DO UPDATE SET label = EXCLUDED.label
RETURNING id, label;

这里EXCLUDED.label指的是插入语句中准备插入的label值，和原表冲突行的label值一致，所以实际不会修改数据，但能触发返回冲突行的ID。

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