PostgreSQL 14通过查询结果更新列后无数据返回问题

正确的操作SQL示例 #

先确保你使用了规范的语句来新增列并填充数据：

1. 新增列（根据pop_in_m实际类型调整字段类型，比如numeric、float）：

ALTER TABLE your_target_table ADD COLUMN testcol numeric;

2. 填充匹配数据（两种可靠写法）：

写法一：使用UPDATE ... FROM关联表

UPDATE your_target_table t
SET testcol = c.pop_in_m
FROM CITIES_BIGGER_THAN_1M c
WHERE t.city_id = c.city_id;

写法二：使用子查询

UPDATE your_target_table t
SET testcol = (
    SELECT c.pop_in_m 
    FROM CITIES_BIGGER_THAN_1M c 
    WHERE c.city_id = t.city_id
);

以上两种写法都会仅更新city_id匹配的行，不匹配的行testcol会保持NULL（即留空）。

排查无匹配数据的核心原因 #

如果执行后testcol非空行仍为0，按以下步骤逐一验证：

• 检查city_id数据类型一致性：若目标表与CITIES_BIGGER_THAN_1M的city_id类型不一致（比如一个是int，一个是varchar），会直接导致匹配失效。执行以下语句验证是否存在匹配行：

SELECT * 
FROM your_target_table t
JOIN CITIES_BIGGER_THAN_1M c ON t.city_id = c.city_id
LIMIT 10;

若返回空结果，说明两张表的city_id无交集。

• 确认CITIES_BIGGER_THAN_1M的有效性：先检查表内是否有数据：

SELECT COUNT(*) FROM CITIES_BIGGER_THAN_1M;

再验证表中city_id是否存在于目标表：

SELECT c.city_id 
FROM CITIES_BIGGER_THAN_1M c
WHERE EXISTS (
    SELECT 1 FROM your_target_table t WHERE t.city_id = c.city_id
);

若返回空，说明两张表的city_id完全不重叠。

• 排查标识符大小写问题：PostgreSQL默认会将未加引号的表名、列名转为小写。如果你的表是通过带引号的方式创建的（比如"CITIES_BIGGER_THAN_1M"），查询时必须带引号，否则会因找不到表导致UPDATE未执行任何操作。

• 验证UPDATE实际影响行数：在UPDATE语句后执行以下命令，查看实际更新的行数：

GET DIAGNOSTICS updated_rows = ROW_COUNT;
SELECT updated_rows;

若返回0，说明确实没有匹配的行被更新。

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