Thank you Adrian - On Fri, Jan 11, 2019 at 4:55 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 1/11/19 4:50 AM, Alexander Farber wrote: > > https://www.db-fiddle.com/f/22jfWnsvqD8hVeFPXsyLbV/0 > > Why not put a test for the block in the function and then use different > UPDATE's depending on the result? > 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
