PostgreSQL问题：无法将查询结果作为新列写入已有表

错误原因 #

你之前的UPDATE语句中，赋值用的子查询返回了表中所有行的结果，数据库无法确定每一行对应哪个值，因此抛出ERROR: subquery returns more than 1 row错误。

解决方案 #

方案1：直接在SET语句中内联计算逻辑

不需要嵌套子查询，直接为每行计算对应的date_day值：

UPDATE "CUSTOM".org 
SET date_day = LEFT(
    CASE
        WHEN dt !~ '^[0-9]' THEN RIGHT(dt, strpos(dt, ',') + 12)
        ELSE dt
    END,
    strpos(
        CASE
            WHEN dt !~ '^[0-9]' THEN RIGHT(dt, strpos(dt, ',') + 12)
            ELSE dt
        END,
        ' '
    ) - 1
);

方案2：通过主键/唯一键关联子查询

如果表有主键（比如id），可以用子查询生成带主键的结果集，再关联主表更新，确保每行对应正确的值：

UPDATE "CUSTOM".org o
SET date_day = orgi.day_
FROM (
    SELECT 
        id, -- 替换成你的表主键列
        LEFT(
            CASE
                WHEN dt !~ '^[0-9]' THEN RIGHT(dt, strpos(dt, ',') + 12)
                ELSE dt
            END,
            strpos(
                CASE
                    WHEN dt !~ '^[0-9]' THEN RIGHT(dt, strpos(dt, ',') + 12)
                    ELSE dt
                END,
                ' '
            ) - 1
        ) AS day_
    FROM "CUSTOM".org
) AS orgi
WHERE o.id = orgi.id; -- 主键关联

如果表没有主键，可以临时用PostgreSQL内部的ctid字段关联（仅临时更新使用，不建议依赖该字段长期操作）：

UPDATE "CUSTOM".org o
SET date_day = orgi.day_
FROM (
    SELECT 
        ctid,
        LEFT(
            CASE
                WHEN dt !~ '^[0-9]' THEN RIGHT(dt, strpos(dt, ',') + 12)
                ELSE dt
            END,
            strpos(
                CASE
                    WHEN dt !~ '^[0-9]' THEN RIGHT(dt, strpos(dt, ',') + 12)
                    ELSE dt
                END,
                ' '
            ) - 1
        ) AS day_
    FROM "CUSTOM".org
) AS orgi
WHERE o.ctid = orgi.ctid;

方案3：使用生成列（自动维护，PostgreSQL 12+支持）

如果你的PostgreSQL版本在12及以上，可以创建存储生成列，让date_day自动随dt列更新，不需要手动执行UPDATE：

-- 先删除之前新增的空列（如果已添加）
ALTER TABLE "CUSTOM".org DROP COLUMN IF EXISTS date_day;

-- 创建生成列
ALTER TABLE "CUSTOM".org 
ADD COLUMN date_day text GENERATED ALWAYS AS (
    LEFT(
        CASE
            WHEN dt !~ '^[0-9]' THEN RIGHT(dt, strpos(dt, ',') + 12)
            ELSE dt
        END,
        strpos(
            CASE
                WHEN dt !~ '^[0-9]' THEN RIGHT(dt, strpos(dt, ',') + 12)
                ELSE dt
            END,
            ' '
        ) - 1
    )
) STORED;

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