如何将带LEFT JOIN的PostGIS SELECT转为UPDATE更新geom字段？

你需要通过关联主键确保每条ptp_links记录匹配对应的生成几何线，避免出现全表geom相同或类型不匹配的错误。以下是正确的UPDATE语句：

UPDATE ptp_links target
SET geom = source.link_geom
FROM (
  SELECT 
    ptp.id,
    ST_MAKELINE(
      ST_POINT(out_bs.longitude::DOUBLE PRECISION, out_bs.latitude::DOUBLE PRECISION, 4326),
      ST_POINT(
        CASE WHEN ptp.link_purpose = 'backhaul' THEN in_bs.longitude::DOUBLE PRECISION ELSE ptp.in_address_geo_x::DOUBLE PRECISION END,
        CASE WHEN ptp.link_purpose = 'backhaul' THEN in_bs.latitude::DOUBLE PRECISION ELSE ptp.in_address_geo_y::DOUBLE PRECISION END,
        4326
      )
    ) AS link_geom
  FROM ptp_links ptp
  LEFT JOIN base_stations in_bs ON ptp.in_base_station = in_bs.id
  LEFT JOIN base_stations out_bs ON ptp.out_base_Station = out_bs.id
) source
WHERE target.id = source.id;

关键说明： #

• 子查询source必须包含ptp.id（假设id是ptp_links的主键），用来和要更新的target表做关联，确保每条记录匹配对应的几何线。
• 给生成的几何线命名为link_geom，明确字段类型，避免PostgreSQL将其识别为record类型。
• 若out_bs或in_bs关联不到数据，ST_MAKELINE会生成NULL，你可以用COALESCE保留原geom值，比如：SET geom = COALESCE(source.link_geom, target.geom)。

你之前的失败原因： #

1. 直接UPDATE ptp_links SET geom = ST_MAKELINE(...)未关联表数据，ST_MAKELINE参数未对应单条记录字段，只会生成固定值覆盖所有行。
2. 使用UPDATE FROM SELECT时未包含主键关联，也未给生成的几何线指定别名，导致PostgreSQL无法识别单个geometry字段，返回整行记录触发类型不匹配错误。

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