From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to always run UPDATE FROM despite missing records in the source table? |
Date: | 2019-01-11 16:51:25 |
Message-ID: | 87k1jbno3w.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>>>>> "Alexander" == Alexander Farber <alexander(dot)farber(at)gmail(dot)com> writes:
Alexander> However the following query does not work as intended and
Alexander> does not update any fields, because there is no matching
Alexander> block in the geoip table found:
Alexander> UPDATE users u SET
Alexander> visited = now(), -- HOW TO ALWAYS UPDATE THIS FIELD?
Alexander> ip = '20.20.20.20'::inet, -- HOW TO ALWAYS UPDATE THIS FIELD?
Alexander> lat = i.lat,
Alexander> lng = i.lng
Alexander> FROM geoip i
Alexander> WHERE u.uid = 2 AND '20.20.20.20'::inet <<= i.block;
Alexander> The field visited and ip however should be always updated -
Alexander> regardless if the block was found or not.
Alexander> Kind of LEFT JOIN, but for UPDATE - how to achieve this please?
It can be done like this (this assumes you want to preserve the previous
values of u.lat/u.lng if the block was not found; if you want to set
them to null instead, then remove the coalesce() calls):
UPDATE users u
SET visited = now(),
ip = v.ip,
lat = coalesce(i.lat, u.lat),
lng = coalesce(i.lng, u.lng)
FROM (VALUES ('20.20.20.20'::inet)) v(ip)
LEFT JOIN geoip i ON (v.ip <<= i.block)
WHERE u.uid = 2;
Alexander> But that would run the same subquery twice (correct?) and my
Alexander> geoip table is already slow with 3073410 records
Slow even with a gist or spgist index? what does the explain analyze
look like?
(You could also try using the ip4r module; I've not done any serious
benchmarking to see if it's faster than the built-in index types, though
it has some theoretical advantages due to not being restricted to CIDR
ranges. In pg versions before the built-in inet type got a gist index
method, ip4r was _the_ way to do ip block lookups for geoip etc.)
--
Andrew (irc:RhodiumToad)
From | Date | Subject | |
---|---|---|---|
Next Message | Mitar | 2019-01-11 18:30:45 | Re: Benchmark of using JSON to transport query results in node.js |
Previous Message | Adrian Klaver | 2019-01-11 15:55:05 | Re: How to always run UPDATE FROM despite missing records in the source table? |