maxmind

Read MaxMind databases (GeoLite, GeoIP, and more)

Maintainer(s): marselester

Installing and Loading

INSTALL maxmind FROM community;
LOAD maxmind;

Example

SELECT geolite_city('./GeoLite2-City.mmdb', '1.0.64.0', '');

About maxmind

Read any MaxMind database using table and scalar functions. GeoLite and GeoIP databases get typed columns; other MMDB files return JSON in a record VARCHAR column.

Scan all IP network blocks with read_mmdb(). Use the optional network parameter to limit the scan to a subnet, e.g., read_mmdb(path, network='1.0.0.0/8'). Empty records are excluded by default; use include_empty=true to include them.

SELECT network, city.names.en
FROM read_mmdb('./GeoLite2-City.mmdb')
WHERE city.names.en IS NOT NULL
LIMIT 1;
┌─────────────┬───────────┐
│   network   │    en     │
├─────────────┼───────────┤
│ 1.0.64.0/20 │ Hiroshima │
└─────────────┴───────────┘

Look up a record by IP address with typed scalar functions that return structs. The third parameter filters which fields to decode for faster lookups (pass '' for all fields).

SELECT geolite_city('./GeoLite2-City.mmdb', '1.0.64.0', 'city').city.names.en AS en;
┌───────────┐
│    en     │
├───────────┤
│ Hiroshima │
└───────────┘

Use mmdb_record() to look up a record from any MMDB file as JSON:

SELECT mmdb_record('./GeoLite2-City.mmdb', '1.0.64.0', 'city')::json -> 'city' -> 'names' ->> 'en' AS en;
┌───────────┐
│    en     │
├───────────┤
│ Hiroshima │
└───────────┘

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings

This extension does not add any settings.