Postgres 16.8中CTE插入后更新同条记录失败求助

问题根源 #

你的语句失效核心原因是字段名time是Postgres的保留关键字，未加双引号引用时会被解析为内置函数/类型，导致字段引用歧义；此外拆分插入和更新操作也增加了不必要的复杂度。

解决方案 #

方案1：修改字段名（推荐） #

避免使用保留关键字作为字段名，将time改为create_time或其他非关键字名称，一步完成插入并计算目标字段：

-- 重建表（已有数据需先迁移）
CREATE TABLE wf_policy
(
    id          BIGSERIAL PRIMARY KEY, 
    name        TEXT   NOT NULL,       
    create_time BIGINT,       
    ver         BIGINT       
);

-- 插入时直接计算ver值
WITH new_id AS (
    SELECT nextval('wf_policy_id_seq') AS id
)
INSERT INTO wf_policy (id, name, create_time, ver)
SELECT id, 'Name', 1000000, id + 1000000
FROM new_id
RETURNING *;

方案2：保留原字段名，修正语句 #

若无法修改字段名，所有引用time字段的地方必须用双引号"time"包裹，修正后的CTE语句如下：

WITH inserted_row AS (
    INSERT INTO wf_policy (name, "time", ver)
    VALUES ('Name', 1000000, 0)
    RETURNING id, "time"
)
UPDATE wf_policy
SET ver = inserted_row."time" + inserted_row.id
FROM inserted_row
WHERE wf_policy.id = inserted_row.id
RETURNING wf_policy.id, wf_policy.name, wf_policy."time", wf_policy.ver;

执行后ver字段会正确设置为id + time的值。

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