sql - 尽管源表中缺少记录，如何始终运行 UPDATE FROM？

Question

我已经准备好了a simplified test case对于我的问题-

在 PostgreSQL 10.6 中有 2 个表:

CREATE TABLE users ( 
  uid SERIAL PRIMARY KEY,
        created       timestamptz NOT NULL,
        visited       timestamptz NOT NULL,
        ip            inet        NOT NULL,
        lat           double precision,
        lng           double precision
  );

  CREATE TABLE geoip (
        block   inet    PRIMARY KEY,
        lat     double precision,
        lng     double precision
);

CREATE INDEX ON geoip USING SPGIST (block);

其中填充了以下测试数据:

INSERT INTO users (created, visited, ip) VALUES
  (now(), now(), '1.2.3.4'::inet),
  (now(), now(), '1.2.3.5'::inet),
  (now(), now(), '1.2.3.6'::inet);

INSERT INTO geoip (block, lat, lng) VALUES
 ('1.2.3.0/24', -33.4940, 143.2104),
 ('10.0.0.0/8', 34.6617, 133.9350);

然后在存储函数中运行以下更新命令 -

UPDATE users u SET
    visited = now(),
    ip      = '10.10.10.10'::inet,
    lat     = i.lat,
    lng     = i.lng
FROM geoip i
WHERE u.uid = 1 AND '10.10.10.10'::inet <<= i.block;

(1 和 ip 地址实际上是我存储函数中的 in_uid 和 in_ip 参数)。

上述查询运行良好并更新了 users 表中的所有 4 个字段。

但是以下查询没有按预期工作并且没有更新任何字段，因为在找到的 geoip 表中没有匹配的 block:

UPDATE users u SET
    visited = now(),               -- HOW TO ALWAYS UPDATE THIS FIELD?
    ip      = '20.20.20.20'::inet, -- HOW TO ALWAYS UPDATE THIS FIELD?
    lat     = i.lat,
    lng     = i.lng
FROM geoip i
WHERE u.uid = 2 AND '20.20.20.20'::inet <<= i.block;

visited 和 ip 字段应该始终更新 - 无论是否找到 block。

有点像 LEFT JOIN，但对于 UPDATE - 请问如何实现？

我能想到的唯一解决方法是 -

UPDATE users SET
    visited = now(),
    ip      = '20.20.20.20'::inet,
    lat     = (SELECT lat FROM geoip WHERE '20.20.20.20'::inet <<= block),
    lng     = (SELECT lng FROM geoip WHERE '20.20.20.20'::inet <<= block)
WHERE uid = 2;

但这会运行相同的子查询两次(正确吗？)并且我的 geoip 表已经很慢了，有 3073410 条记录(这就是为什么我试图缓存它的lat 和 lng 值在每个用户登录事件的 users 表中)

Answer 1

我的建议(也许是愚蠢的)是添加 u.uid = 2 OR (u.uid = 2 AND '20.20.20.20'::inet <<= i.block)安装了那个AND条件..可能会改变lat = i.lat , 至 lat = NULLIF(i.lat, 0)