PostgreSQL中基于另一表条件给列赋值生成新表的实现方法

你要的区间赋值逻辑可以直接通过PostgreSQL原生的CASE条件表达式实现，不需要写循环逻辑，SQL为声明式语法，直接定义字段映射规则即可生效。

核心映射逻辑 #

区间到cole值的对应规则用CASE语句实现如下：

CASE
    WHEN colc >= 0 AND colc < 50 THEN 0
    WHEN colc >= 50 AND colc < 100 THEN 1
    WHEN colc >= 100 THEN 2
    -- 可选：补充ELSE分支处理NULL、负数等异常值，例如 ELSE -1
END AS cole

场景1：直接创建table3并写入数据 #

如果table3尚未创建，推荐直接用CREATE TABLE AS语句一次性生成带数据的新表，可根据实际需求调整关联逻辑、选择需要保留的其他字段：

需关联table2的写法 #

CREATE TABLE table3 AS
SELECT
    -- 按需选择table1、table2需要保留的字段，以下为示例
    t1.*,
    t2.*,
    CASE
        WHEN t1.colc >= 0 AND t1.colc < 50 THEN 0
        WHEN t1.colc >= 50 AND t1.colc < 100 THEN 1
        WHEN t1.colc >= 100 THEN 2
    END AS cole
FROM table1 t1
-- 替换为table1和table2的实际关联条件
JOIN table2 t2 ON t1.关联字段 = t2.关联字段;

无需关联table2的写法 #

如果table3只需要用到table1的字段，可去掉关联部分：

CREATE TABLE table3 AS
SELECT
    *,
    CASE
        WHEN colc >= 0 AND colc < 50 THEN 0
        WHEN colc >= 50 AND colc < 100 THEN 1
        WHEN colc >= 100 THEN 2
    END AS cole
FROM table1;

场景2：已预先创建table3，需更新cole字段 #

如果table3已经创建完成，仅需要对cole字段赋值，可使用UPDATE语句：

UPDATE table3
SET cole = CASE
    WHEN colc >= 0 AND colc < 50 THEN 0
    WHEN colc >= 50 AND colc < 100 THEN 1
    WHEN colc >= 100 THEN 2
END;

如果table3的colc字段需要关联table1获取，使用关联更新写法：

UPDATE table3 t3
SET cole = CASE
    WHEN t1.colc >= 0 AND t1.colc < 50 THEN 0
    WHEN t1.colc >= 50 AND t1.colc < 100 THEN 1
    WHEN t1.colc >= 100 THEN 2
END
FROM table1 t1
-- 替换为实际关联条件
WHERE t3.table1_id = t1.id;

注意事项 #

• 如果colc存在负数、NULL等不符合区间要求的值，可在CASE语句末尾补充ELSE分支设置默认值，方便后续识别异常数据
• 可在建表后给cole字段添加CHECK约束ALTER TABLE table3 ADD CHECK (cole IN (0,1,2))，避免非法值写入

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