Skip to main content

inet Extension

The inet extension defines the INET data type for storing IPv4 and IPv6 Internet addresses. It supports the CIDR notation for subnet masks (e.g., 198.51.100.0/22, 2001:db8:3c4d::/48).

Installing and Loading​

The inet extension will be transparently autoloaded on first use from the official extension repository. If you would like to install and load it manually, run:

INSTALL inet;
LOAD inet;

Examples​

SELECT '127.0.0.1'::INET AS ipv4, '2001:db8:3c4d::/48'::INET AS ipv6;
ipv4ipv6
127.0.0.12001:db8:3c4d::/48
CREATE TABLE tbl (id INTEGER, ip INET);
INSERT INTO tbl VALUES
    (1, '192.168.0.0/16'),
    (2, '127.0.0.1'),
    (3, '8.8.8.8'),
    (4, 'fe80::/10'),
    (5, '2001:db8:3c4d:15::1a2f:1a2b');
SELECT * FROM tbl;
idip
1192.168.0.0/16
2127.0.0.1
38.8.8.8
4fe80::/10
52001:db8:3c4d:15::1a2f:1a2b

Operations on INET Values​

INET values can be compared naturally, and IPv4 will sort before IPv6. Additionally, IP addresses can be modified by adding or subtracting integers.

CREATE TABLE tbl (cidr INET);
INSERT INTO tbl VALUES
    ('127.0.0.1'::INET + 10),
    ('fe80::10'::INET - 9),
    ('127.0.0.1'),
    ('2001:db8:3c4d:15::1a2f:1a2b');
SELECT cidr FROM tbl ORDER BY cidr ASC;
cidr
127.0.0.1
127.0.0.11
2001:db8:3c4d:15::1a2f:1a2b
fe80::7

host Function​

The host component of an INET value can be extracted using the HOST() function.

CREATE TABLE tbl (cidr INET);
INSERT INTO tbl VALUES
    ('192.168.0.0/16'),
    ('127.0.0.1'),
    ('2001:db8:3c4d:15::1a2f:1a2b/96');
SELECT cidr, host(cidr) AS host FROM tbl;
cidrhost
192.168.0.0/16192.168.0.0
127.0.0.1127.0.0.1
2001:db8:3c4d:15::1a2f:1a2b/962001:db8:3c4d:15::1a2f:1a2b

netmask Function​

Computes the network mask for the address's network.

CREATE TABLE tbl (cidr INET);
INSERT INTO tbl VALUES
    ('192.168.1.5/24'),
    ('127.0.0.1'),
    ('2001:db8:3c4d:15::1a2f:1a2b/96');
SELECT cidr, netmask(cidr) AS netmask FROM tbl;
cidrnetmask
192.168.1.5/24255.255.255.0/24
127.0.0.1255.255.255.255
2001:db8:3c4d:15::1a2f:1a2b/96ffff:ffff:ffff:ffff:ffff:ffff::/96

network Function​

Returns the network part of the address, zeroing out whatever is to the right of the netmask.

CREATE TABLE tbl (cidr INET);
INSERT INTO tbl VALUES
    ('192.168.1.5/24'),
    ('127.0.0.1'),
    ('2001:db8:3c4d:15::1a2f:1a2b/96');
SELECT cidr, network(cidr) AS network FROM tbl;
cidrnetwork
192.168.1.5/24192.168.1.0/24
127.0.0.1127.0.0.1
2001:db8:3c4d:15::1a2f:1a2b/962001:db8:3c4d:15::/96

broadcast Function​

Computes the broadcast address for the address's network.

CREATE TABLE tbl (cidr INET);
INSERT INTO tbl VALUES
    ('192.168.1.5/24'),
    ('127.0.0.1'),
    ('2001:db8:3c4d:15::1a2f:1a2b/96');
SELECT cidr, broadcast(cidr) AS broadcast FROM tbl;
cidrbroadcast
192.168.1.5/24192.168.1.255/24
127.0.0.1127.0.0.1
2001:db8:3c4d:15::1a2f:1a2b/962001:db8:3c4d:15::ffff:ffff/96

<<= Predicate​

Is subnet contained by or equal to subnet?

CREATE TABLE tbl (cidr INET);
INSERT INTO tbl VALUES
    ('192.168.1.0/24'),
    ('127.0.0.1'),
    ('2001:db8:3c4d:15::1a2f:1a2b/96');
SELECT cidr, INET '192.168.1.5/32' <<= cidr AS subnet_contained FROM tbl;
cidrsubnet_contained
192.168.1.0/24true
127.0.0.1false
2001:db8:3c4d:15::1a2f:1a2b/96false

>>= Predicate​

Does subnet contain or equal subnet?

CREATE TABLE tbl (cidr INET);
INSERT INTO tbl VALUES
    ('192.168.1.0/24'),
    ('127.0.0.1'),
    ('2001:db8:3c4d:15::1a2f:1a2b/96');
SELECT cidr, INET '192.168.0.0/16' >>= cidr AS subnet_contains FROM tbl;
cidrsubnet_contains
192.168.1.0/24true
127.0.0.1false
2001:db8:3c4d:15::1a2f:1a2b/96false

HTML Escape and Unescape Functions​

SELECT html_escape('&');
┌──────────────────┐
│ html_escape('&') │
│     varchar      │
├──────────────────┤
│ &amp;            │
└──────────────────┘
SELECT html_unescape('&amp;');
┌────────────────────────┐
│ html_unescape('&amp;') │
│        varchar         │
├────────────────────────┤
│ &                      │
└────────────────────────┘