PostgreSQL中st_length结果插入cost列与对应行不匹配问题

我是SQL/PostgreSQL新手，想对planet_osm_roads表的geometry类型字段way执行st_length函数，把计算出的长度存入新增的浮点型列cost。但执行以下命令后结果不符合预期：

alter table planet_osm_roads add cost float;
insert into planet_osm_roads (cost)
select st_length(st_transform(way, 4326)::geography) from planet_osm_roads;

示例结果 #

带有source和target值的行，cost列为空；cost列有值的行，source和target为空——计算出的长度未关联到对应的linestring行，期望每行都能正确填入对应cost值。

解决方案 #

问题出在使用了INSERT语句，它会新增一批仅包含cost值的空行，而非更新原有行的cost字段。正确做法是用UPDATE语句，将计算结果匹配到每一行：

-- 新增cost列（此步骤已正确执行）
alter table planet_osm_roads add cost float;

-- 更新原有行的cost字段，基于当前行的way计算长度
update planet_osm_roads
set cost = st_length(st_transform(way, 4326)::geography);

如果表存在主键（比如osm_id），也可以用关联写法（上述写法已满足需求，此为可选方案）：

update planet_osm_roads r1
set cost = r2.calc_length
from (
    select osm_id, st_length(st_transform(way, 4326)::geography) as calc_length
    from planet_osm_roads
) r2
where r1.osm_id = r2.osm_id;

执行后，每一行的cost字段都会填充对应way的长度值，不会新增空行。

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