Postgres `CREATE TABLE` 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 

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
);

IP to Company Database 

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
);

ASN Database 

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
);

IP to Privacy Detection Database 

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
);

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 

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
);

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
);