sql - 保存查询结果而不将其附加到 PostgreSQL 中的现有表中

Question

我有一个包含和列的表路和一个空列。使用以下脚本：gidgeomdistance

INSERT INTO road (distance) 
SELECT ST_length(a.geom::geography,true)/1000 
FROM road a;

我只得到附加到现有路标表的结果。我想知道是否有办法从distance列的开头保存我的查询结果，以便它们与现有的gid;

标签： sqlpostgresqlinsert

Answer 1

you probably are looking for update?

update road set distance =  ST_length(geom::geography,true)/1000;

try it within a transaction to be able to rollback:

begin;
    update road set distance =  ST_length(geom::geography,true)/1000;
    select * from road; --to check the result
rollback; -- or commit if you like it