PostgreSQL: `CREATE TABLE` and `CREATE FUNCTION` (UDF) queries for IPinfo data downloads

Docs Database Downloads
,
1

Postgres CREATE TABLE queries for IPinfo data downloads

We recommend you go through our Postgres doc first if you have not already: Using IPinfo’s data downloads in PostgreSQL

You need to install the IP4R extension to use the ipaddress and iprange data types. After installing the extension, you need to import the extension with:

CREATE EXTENSION ip4r;

IP to Geolocation Database

  • Table Name: ip_location
  • UDF Name: ip_location

Create table query

CREATE TABLE ip_location (
  start_ip ipaddress,
  end_ip ipaddress,
  join_key ipaddress,
  ip_range iprange generated always as (iprange(start_ip, end_ip)) stored,
  city text,
  region text,
  country text,
  latitude text,
  longitude text,
  postal_code text,
  timezone text
);

UDF Query

CREATE OR REPLACE FUNCTION ip_location(log_table TEXT, ip TEXT)
  RETURNS TABLE (input_ip TEXT, city TEXT, region TEXT, country TEXT, latitude TEXT, longitude TEXT, postal_code TEXT, timezone TEXT)  AS $$
BEGIN
  RETURN QUERY EXECUTE '
  	SELECT l.' || ip || ', loc.city, loc.region, loc.country, loc.latitude, loc.longitude, loc.postal_code, loc.timezone
	FROM ' || log_table || ' l
	JOIN ip_location loc
	ON loc.ip_range && l.' || ip || '::ipaddress; -- casting ip as ipaddress data type
  ';
END;
$$ LANGUAGE plpgsql;

Usage of the UDF

SELECT *
FROM ip_location('log', 'ip');

IP to Company Database

  • Table Name: ip_company
  • UDF Name: ip_company

Create table query

CREATE TABLE ip_company (
  start_ip ipaddress,
  end_ip ipaddress,
  join_key ipaddress,
  ip_range iprange generated always as (iprange(start_ip, end_ip)) stored,
  name text,
  domain text,
  type text,
  asn text,
  as_name text,
  as_domain text,
  as_type text,
  country text
);

UDF Query

CREATE OR REPLACE FUNCTION ip_company(log_table TEXT, ip TEXT)
  RETURNS TABLE (input_ip TEXT, name TEXT, domain TEXT, type TEXT, asn TEXT, as_name TEXT, as_domain TEXT, as_type TEXT, country TEXT)  AS $$
BEGIN
  RETURN QUERY EXECUTE '
  	SELECT l.' || ip || ', ipc.name, ipc.domain, ipc.type, ipc.asn, ipc.as_name, ipc.as_domain, ipc.as_type, ipc.country
	FROM ' || log_table || ' l
	JOIN ip_company ipc
	ON ipc.ip_range && l.' || ip || '::ipaddress; -- casting ip as ipaddress data type
  ';
END;
$$ LANGUAGE plpgsql;

Usage of the UDF

SELECT *
FROM ip_company('log', 'ip');

ASN Database

  • Table Name: ip_asn
  • UDF Name: ip_asn

Create table query

CREATE TABLE ip_asn (
  start_ip ipaddress,
  end_ip ipaddress,
  join_key ipaddress,
  ip_range iprange generated always as (iprange(start_ip, end_ip)) stored,
  asn text,
  domain text,
  name text,
  type text,
  country text
);

UDF Query

CREATE OR REPLACE FUNCTION ip_asn(log_table TEXT, ip TEXT)
  RETURNS TABLE (input_ip TEXT, asn TEXT, domain TEXT, name TEXT, type TEXT, country TEXT)  AS $$
BEGIN
  RETURN QUERY EXECUTE '
  	SELECT l.' || ip || ', asn.asn, asn.domain, asn.name, asn.type, asn.country
	FROM ' || log_table || ' l
	JOIN ip_asn asn
	ON asn.ip_range && l.' || ip || '::ipaddress; -- casting ip as ipaddress data type
  ';
END;
$$ LANGUAGE plpgsql;

Usage of the UDF

SELECT *
FROM ip_asn('log', 'ip');

IP to Privacy Detection Database

  • Table Name: ip_privacy
  • UDF Name: ip_privacy

Create table query

CREATE TABLE ip_privacy (
  start_ip ipaddress,
  end_ip ipaddress,
  join_key ipaddress,
  ip_range iprange generated always as (iprange(start_ip, end_ip)) stored,
  hosting text,
  proxy text,
  tor text,
  vpn text,
  relay text,
  service text
);

UDF Query

CREATE OR REPLACE FUNCTION ip_privacy(log_table TEXT, ip TEXT)
  RETURNS TABLE (input_ip TEXT, hosting TEXT, proxy TEXT, tor TEXT, vpn TEXT, relay TEXT, service TEXT)  AS $$
BEGIN
  RETURN QUERY EXECUTE '
  	SELECT l.' || ip || ', privacy.hosting, privacy.proxy, privacy.tor, privacy.vpn, privacy.relay, privacy.service
	FROM ' || log_table || ' l
	JOIN ip_privacy privacy
	ON privacy.ip_range && l.' || ip || '::ipaddress; -- casting ip as ipaddress data type
  ';
END;
$$ LANGUAGE plpgsql;

Usage of the UDF

SELECT *
FROM ip_privacy('log', 'ip');

IP to Mobile Carrier Database

CREATE TABLE ip_carrier (
  start_ip ipaddress,
  end_ip ipaddress,
  join_key ipaddress,
  ip_range iprange generated always as (iprange(start_ip, end_ip)) stored,
  name text,
  country text,
  mcc integer,
  mnc integer
);

Hosted Domains

CREATE TABLE hosted_domains (
  ip ipaddress,
  total integer,
  domains text
);

Please look into the string_to_array (doc) function to convert the domains column from text data type to array data type.

Also this database does not contains start_ip, end_ip and join_key.

Abuse Contact Database

CREATE TABLE ip_abuse (
  start_ip ipaddress,
  end_ip ipaddress,
  join_key ipaddress,
  ip_range iprange generated always as (iprange(start_ip, end_ip)) stored,
  name text,
  email text,
  address text,
  country text,
  phone text
);

IP to Country Database

  • Table Name: ip_country
  • UDF Name: ip_country

Create table query

CREATE TABLE ip_country (
  start_ip ipaddress,
  end_ip ipaddress,
  ip_range iprange generated always as (iprange(start_ip, end_ip)) stored,
  country text,
  country_name text,
  continent text,
  continent_name text
);

UDF Query

CREATE OR REPLACE FUNCTION ip_country(log_table TEXT, ip TEXT)
  RETURNS TABLE (input_ip TEXT, country TEXT, country_name TEXT, continent TEXT, continent_name TEXT)  AS $$
BEGIN
  RETURN QUERY EXECUTE '
  	SELECT l.' || ip || ', ipc.country, ipc.country_name, ipc.continent, ipc.continent_name
	FROM ' || log_table || ' l
	JOIN ip_country ipc
	ON ipc.ip_range && l.' || ip || '::ipaddress; -- casting ip as ipaddress data type
  ';
END;
$$ LANGUAGE plpgsql;

Usage of the UDF

SELECT *
FROM ip_country('log', 'ip');

IP to ASN Database

CREATE TABLE ip_asn (
  start_ip ipaddress,
  end_ip ipaddress,
  ip_range iprange generated always as (iprange(start_ip, end_ip)) stored,
  asn text,
  name text,
  domain text
);

IP to Country + ASN Database

CREATE TABLE ip_country_asn (
  start_ip ipaddress,
  end_ip ipaddress,
  ip_range iprange generated always as (iprange(start_ip, end_ip)) stored,
  country text,
  country_name text,
  continent text,
  continent_name text,
  asn text,
  as_name text,
  as_domain text
);

IP to Privacy Detection Extended Database

CREATE TABLE ip_privacy (
  start_ip ipaddress,
  end_ip ipaddress,
  ip_range iprange generated always as (iprange(start_ip, end_ip)) stored,
  hosting text,
  proxy text,
  tor text,
  vpn text,
  relay text,
  vpn_name text,
  anycast text,
  census text,
  device_activity text,
  whois text,
  vpn_config text,
  census_port integer
);

IP to Geolocation Extended Database

CREATE TABLE ip_location (
  start_ip ipaddress,
  end_ip ipaddress,
  join_key ipaddress,
  ip_range iprange generated always as (iprange(start_ip, end_ip)) stored,
  city text,
  region text,
  country text,
  latitude text,
  longitude text,
  postal_code text,
  timezone text,
  geoname_id integer,
  radius integer
);