PostgreSQL关联结果插入前值转换失效问题求助

• UPDATE语句未生效：执行UPDATE时prod.result表为空，没有匹配到任何可更新的行，这条语句实际没有产生任何数据变更。
• INSERT未做数值转换：真正插入数据的是后续的INSERT语句，但你在SELECT子句中直接使用cust.age，未添加+5的转换逻辑，所以最终插入的age是原始值。

不需要先执行UPDATE，直接在INSERT的SELECT语句中对age做转换即可，根据不同场景选择对应写法：

场景1：首次插入数据 #

直接执行带数值转换的INSERT语句：

INSERT INTO prod.result(
    customerid
    ,first
    ,last
    ,age 
    ,policytype
)
SELECT  
    cust.customerid
    ,cust.first
    ,cust.last
    ,cust.age + 5  -- 在此处完成数值转换
    ,pol.policytype
FROM staging.customers cust
JOIN prod.policies pol
ON cust.customerid = pol.customerid;

场景2：清空旧数据后插入新数据 #

如果需要避免重复数据，可先清空表再插入（注意：TRUNCATE会删除表所有数据，操作前确认数据无需保留）：

TRUNCATE TABLE prod.result;

INSERT INTO prod.result(
    customerid
    ,first
    ,last
    ,age 
    ,policytype
)
SELECT  
    cust.customerid
    ,cust.first
    ,cust.last
    ,cust.age + 5
    ,pol.policytype
FROM staging.customers cust
JOIN prod.policies pol
ON cust.customerid = pol.customerid;

场景3：更新已有数据+插入新数据 #

假设customerid是prod.result的主键或唯一约束，可使用PostgreSQL的INSERT ... ON CONFLICT语法实现 Upsert：

INSERT INTO prod.result(
    customerid
    ,first
    ,last
    ,age 
    ,policytype
)
SELECT  
    cust.customerid
    ,cust.first
    ,cust.last
    ,cust.age + 5
    ,pol.policytype
FROM staging.customers cust
JOIN prod.policies pol
ON cust.customerid = pol.customerid
ON CONFLICT (customerid) DO UPDATE
SET 
    first = EXCLUDED.first,
    last = EXCLUDED.last,
    age = EXCLUDED.age,  -- 用EXCLUDED获取插入时的转换后值
    policytype = EXCLUDED.policytype;

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