Hologres provides built-in functions for resolving IPv4 addresses to geographic and network metadata—country, province, city, and Internet Service Provider (ISP)—and for converting between IP address strings and integers.
Prerequisites
Before you begin, ensure that you have:
An active Hologres instance connected to a development tool. See Connect to HoloWeb and execute a query.
Submitted a ticket to Hologres technical support to enable the IP address resolution extension for your instance.Submit a ticket
Had the instance Superuser run the following command once per database:
CREATE EXTENSION ipdb;This is a database-level command. Run it once per database. If you switch to a new database, the Superuser must run it again.
Limitations
All IP address resolution functions support IPv4 only.
The functions are provided as an extension (
ipdb) and are not enabled by default. See the Prerequisites section for activation steps.
Function overview
| Function | Description | Example |
|---|---|---|
ip2region | Returns the country, province, city, or ISP for an IPv4 address. Supports Chinese and English output. | ip2region('1.2.3.4', 'country', 'EN') |
ip_country | Returns the country for an IPv4 address. | ip_country('1.2.3.4') |
ip_province | Returns the province for an IPv4 address. | ip_province('1.2.3.4') |
ip_city | Returns the city for an IPv4 address. | ip_city('1.2.3.4') |
ip_isp | Returns the ISP for an IPv4 address. | ip_isp('1.2.3.4') |
ip2long | Converts an IPv4 address to a bigint. | ip2long('1.2.3.4') |
long2ip | Converts a bigint to an IPv4 address string. | long2ip(16909060) |
ip2region
Returns the country, province, city, or ISP for an IPv4 address. Supports Chinese and English output via the lang parameter.
Syntax
ip2region(ip, level, lang)Arguments
| Argument | Type | Description |
|---|---|---|
ip | text | The IPv4 address to look up. |
level | text | The geographic or network level to return. Valid values: Country, Province, City, ISP. |
lang | text | The language of the returned result. Valid values: CN (Chinese), EN (English). |
Return value
text
Examples
Get the country in Chinese:
SELECT ip2region('31.13.79.1', 'country', 'CN');Get the country in English:
SELECT ip2region('31.13.79.1', 'country', 'EN');Get the province:
SELECT ip2region('31.13.79.1', 'province', 'EN');Get the city:
SELECT ip2region('31.13.79.1', 'city', 'EN');Get the ISP:
SELECT ip2region('31.13.79.1', 'isp', 'EN');ip_country, ip_province, ip_city, and ip_isp
These are simplified single-level wrappers around ip2region. Each function returns one geographic or network attribute for an IPv4 address. The default output language is English.
Syntax
ip_country(ip [, lang])
ip_province(ip [, lang])
ip_city(ip [, lang])
ip_isp(ip [, lang])Arguments
| Argument | Type | Description |
|---|---|---|
ip | text | The IPv4 address to look up. |
lang | text | (Optional) The language of the returned result. Valid values: CN (Chinese), EN (English). Defaults to EN. |
Return value
text
Examples
Get the country (defaults to English):
SELECT ip_country('31.13.79.1');Get the country in Chinese:
SELECT ip_country('31.13.79.1', 'CN');Get the province:
SELECT ip_province('31.13.79.1');
SELECT ip_province('31.13.79.1', 'EN');Get the city:
SELECT ip_city('31.13.79.1');
SELECT ip_city('31.13.79.1', 'EN');Get the ISP:
SELECT ip_isp('115.239.210.27');
SELECT ip_isp('115.239.210.27', 'EN');ip2long
Converts an IPv4 address string to a bigint. Useful for storing or comparing IP addresses as integers.
Syntax
ip2long(ip)Arguments
| Argument | Type | Description |
|---|---|---|
ip | text | The IPv4 address to convert. |
Return value
bigint
Example
SELECT ip2long('31.13.79.1');long2ip
Converts a bigint to an IPv4 address string. Use this to reverse the output of ip2long.
Syntax
long2ip(longVal)Arguments
| Argument | Type | Description |
|---|---|---|
longVal | text | The integer value to convert. |
Return value
text
Example
Round-trip conversion from an IP address string to an integer and back:
SELECT long2ip(ip2long('31.13.79.1'));