Re: How to always run UPDATE FROM despite missing records in thesource table?

On Fri, Jan 11, 2019 at 4:55 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

I didn't want to use IF and switch that statement to PL/pgSQL from pure SQL, so Andrew's answer

 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;

suits me better, even though I wonder what is the (VALUES ('20.20.20.20'::inet)) v(ip) construct there, some temporary table which is then LEGT JOINed to the geoip table?

Also, Andrew you have been right - with spgist index my queries against geoip are fast enough, I was looking at the wrong spot in my EXPLAIN ANALYZE output (the average values are slow, I am going to cache them soon)

Regards

Alex