PostgreSQL – Update Column B Using Function with Column A Values

postgresql

Pretty new to SQL.

I have a table (client_info) in PostgreSQL (v11.1) that includes the following columns:

+----------+------------------+
| clientip | clientip_country |
+----------+------------------+
|  8.8.8.8 | null             |
|  1.1.1.1 | null             |
|  4.4.4.4 | null             |
+----------+------------------+

I'm trying to create a function/script that will lookup the country ISO code for each clientip using a local install of the MaxMind GeoLite2 database and then update the corresponding clientip_country with the result to give:

+----------+------------------+
| clientip | clientip_country |
+----------+------------------+
|  8.8.8.8 | US               |
|  1.1.1.1 | AU               |
|  4.4.4.4 | US               |
+----------+------------------+

What's the best way to go about achieving this?

For example I'm able to retrieve the country ISO code of an IP using the following bash command:

$ mmdblookup -f /usr/share/GeoIP/GeoLite2-Country.mmdb -i 1.1.1.1 country iso_code | sed -e '/^$/d' -e 's/.*"\(.*\)".*/\1/'
AU

What direction should I be looking to? An SQL command that queries the GeoLite2 DB? Or a bash script that queries the Postgres DB and updates the clientip_country column with the results?

Best Answer

On @Akina's and @a_horse_with_no_name's advice I downloaded & imported MaxMind's GeoLite2 Country database (in CSV format) using the following:

create table geoip_blocks (
    network cidr,
    geoname_id bigint,
    registered_country_geoname_id bigint,
    represented_country_geoname_id bigint,
    is_anonymous_proxy bool,
    is_satellite_provider bool
);

copy geoip_blocks from 'C:\tmp\GeoLite2-Country-Blocks-IPv4.csv' delimiter ',' csv header;
copy geoip_blocks from 'C:\tmp\GeoLite2-Country-Blocks-IPv6.csv' delimiter ',' csv header;

create index geoip_blocks_network_idx on geoip_blocks using gist (network inet_ops);

create table geoip_locations (
    geoname_id bigint,
    locale_code varchar(2),
    continent_code varchar(2),
    continent_name varchar(255),
    country_iso_code varchar(2),
    country_name varchar(255),
    eu_member bool
);

copy geoip_locations from 'C:\tmp\GeoLite2-Country-Locations-en.csv' delimiter ',' csv header;

then ran an UPDATE to populate my clientip_country column with the appropriate 2 letter ISO country code using the clientip column data:

update
    client_info
set
    clientip_country = country_iso_code
from
    geoip_blocks
    inner join geoip_locations on geoip_blocks.geoname_id = geoip_locations.geoname_id
where
    network >>= client_info.clientip::inet
    and clientip_country is null;

Took a couple of minutes to update just over 100,000 records. Nice.

Related Question