How to always run UPDATE FROM despite missing records in the source table?

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: How to always run UPDATE FROM despite missing records in the source table?
Date: 2019-01-11 12:50:38
Message-ID: CAADeyWh-_Co2h9oq2vp=qMoGAwyMH3v7FG9BFMG0WwKP-b_u4A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Brannen 2019-01-11 15:42:50 RE: How to always run UPDATE FROM despite missing records in the source table?
Previous Message Willy-Bas Loos 2019-01-11 12:50:34 Re: log level of "drop cascade" lists