How to always run UPDATE FROM despite missing records in the source table? - Mailing list pgsql-general

Good afternoon

I have prepared a simplified test case for my question: https://www.db-fiddle.com/f/22jfWnsvqD8hVeFPXsyLbV/0

In PostgreSQL 10.6 there are 2 tables:

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);

which are filled with the following test data:

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);

Then in a stored function I run the following UPDATE command -

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;

(the 1 and the ip address are actually in_uid and in_ip parameters in my stored function).

The above query works well and updates all 4 fields in the users table.

However the following query does not work as intended and does not update any fields, because there is no matching block in the geoip table found:

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;

The field visited and ip however should be always updated - regardless if the block was found or not.

Kind of LEFT JOIN, but for UPDATE - how to achieve this please?

The only workaround that I could think of is -

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;

But that would run the same subquery twice (correct?) and my geoip table is already slow with 3073410 records (and that is why I am trying to cache its lat and lng values in the users table on each user login event)

Regards

Alex