无需使用ALTER TABLE向基于CTE的视图新增id列的可行性咨询

结论 #

可以实现，但不能直接使用ALTER TABLE语句操作CTE或视图：CTE是查询执行过程中生成的临时结果集，没有持久化存储为实体表，视图本身也仅存储查询定义而非实际数据，因此不支持ALTER TABLE加列的操作。

具体实现方案 #

你可以根据需求选择以下两种方案：

• 如果你需要每次查询时动态生成自增序号作为id，直接修改视图定义，在最终SELECT语句中使用窗口函数row_number()生成即可：

CREATE OR REPLACE VIEW someView AS 
WITH foo AS (
    SELECT id FROM bar
        JOIN ...
), foo2 AS (
    SELECT whatever FROM bar2 ...
), ... AS ( ...
), lastResult AS (
   ...
) 
-- 新增id列，如需固定排序规则可在OVER中添加ORDER BY 对应列
SELECT row_number() OVER () AS id, * FROM lastResult;

如果不需要修改视图定义，仅临时查询时需要id列，也可以直接在查询视图时新增字段：

SELECT row_number() OVER () AS id, * FROM someView;

• 如果你需要持久化、唯一且固定不变的id，需要先将CTE的结果持久化为实体表，再使用你提到的ALTER TABLE语句添加主键列：

-- 先将查询结果持久化为实体表
CREATE TABLE materialized_result AS 
WITH foo AS (
    SELECT id FROM bar
        JOIN ...
), foo2 AS (
    SELECT whatever FROM bar2 ...
), ... AS ( ...
), lastResult AS (
   ...
) SELECT * FROM lastResult;

-- 再添加自增主键
ALTER TABLE materialized_result ADD COLUMN id SERIAL PRIMARY KEY;

内容的提问来源于stack exchange，提问作者four-eyes