代码之家 › 专栏 › 技术社区 › Alexander Farber

如何始终从运行更新,尽管源表中缺少记录?

postgresql-10 left-join sql-update postgresql sql

1

Alexander Farber · 技术社区 · 6 年前

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

PostgreSQL 10.6中有两个表:

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 表。

但是,下列查询不能按预期工作,并且不会更新任何字段,因为没有匹配的字段 block 在 geoip 表发现:

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 但是,应始终更新-无论 块 是否找到。

有点左连接,但为了更新-请如何实现这一点?

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

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;

但这将运行同一个子查询两次(正确吗?)还有我的 地磁 桌子已经慢了307340张唱片了(这就是为什么我要尝试 隐藏物 它的 lat 和 lng 价值观 用户 每个用户登录事件的表)

3 回复 | 直到 6 年前

1

Bogdan Trusca 6 年前

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

2

1

dani herrera 6 年前

您不需要查找 geoip 大桌子两次:

-- start transaction
-- some stuff

UPDATE users u SET
    visited = now(),               
    ip      = '20.20.20.20'::inet 
WHERE u.uid = 2;  -- fast because is from pk

UPDATE users u SET
    lat     = i.lat,
    lng     = i.lng
FROM geoip i
WHERE u.uid = 2 AND '20.20.20.20'::inet <<= i.block;

-- more stuff
-- commit tx

3

0

Alexander Farber 6 年前

Andrew Gierth先生 pgsql-general 邮件列表提供了一个仅限SQL的答案:

UPDATE users u SET 
    visited = now(),
    ip = v.ip,
    lat = i.lat,
    lng = i.lng
FROM (VALUES ('20.20.20.20'::inet)) v(ip)
      LEFT JOIN geoip i ON (v.ip <<= i.block)
WHERE u.uid = 2;