Skip to content
InternetData

Load a database into PostgreSQL

Import an InternetData CSV database into PostgreSQL, index it, and look an IP address up in it with one SQL query, IPv4 and IPv6 alike.

Every database comes as gzipped CSV, one IP range per row. This loads IP Country, an Open database, with no extension needed.

1. Download it

bash
curl -L -o ip_country_v1.csv.gz \
  "https://internetdata.io/api/v2/database/download?id=ip_country_v1&format=csvgz&apikey=$INTERNETDATA_API_KEY"

2. Create the table and load it

sql
CREATE TABLE ip_country (
    start_ip inet NOT NULL,
    end_ip inet NOT NULL,
    continent text,
    continent_name text,
    country text,
    country_name text,
    timezone text,
    currency_code text,
    currency_name text,
    currency_symbol text,
    flag_emoji text,
    flag_unicode text
);

\copy ip_country FROM PROGRAM 'gzip -dc ip_country_v1.csv.gz' WITH (FORMAT csv, HEADER true)

CREATE INDEX ON ip_country (start_ip);

The columns follow the CSV's own order, which the database's page lists; an empty cell loads as NULL.

3. Look an address up

The range holding an address is the one with the greatest start_ip at or below it, if its end_ip reaches the address:

sql
SELECT country, country_name
FROM (
    SELECT * FROM ip_country
    WHERE start_ip <= '8.8.8.8'
    ORDER BY start_ip DESC
    LIMIT 1
) r
WHERE '8.8.8.8' <= end_ip;

The index answers it in well under a millisecond. IPv6 works the same, since inet orders IPv4 and IPv6 apart.

4. Refresh it daily

Load each day's file into a new table and swap the names in one transaction, so a query never sees half a load. Metadata says whether a new build is out.