inet 扩展

inet 扩展定义了 INET 数据类型，用于存储 IPv4 与 IPv6 地址。它支持使用 CIDR notation 表示子网掩码（例如 198.51.100.0/22、2001:db8:3c4d::/48）。

安装与加载

inet 扩展首次使用时会从官方扩展仓库自动按需加载。 若要手动安装并加载，请执行：

INSTALL inet;
LOAD inet;

示例

SELECT '127.0.0.1'::INET AS ipv4, '2001:db8:3c4d::/48'::INET AS ipv6;

ipv4	ipv6
127.0.0.1	2001: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;

id	ip
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

INET 值上的操作

INET 值支持自然比较，且 IPv4 会排在 IPv6 之前。此外，IP 地址可通过加减整数进行偏移。

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 函数

INET 值中的主机部分可通过 HOST() 函数提取。

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;

cidr	host
192.168.0.0/16	192.168.0.0
127.0.0.1	127.0.0.1
2001:db8:3c4d:15::1a2f:1a2b/96	2001:db8:3c4d:15::1a2f:1a2b

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, netmask(cidr) AS netmask FROM tbl;

cidr	netmask
192.168.1.5/24	255.255.255.0/24
127.0.0.1	255.255.255.255
2001:db8:3c4d:15::1a2f:1a2b/96	ffff:ffff:ffff:ffff:ffff:ffff::/96

network 函数

返回地址中的网络部分，并将 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;

cidr	network
192.168.1.5/24	192.168.1.0/24
127.0.0.1	127.0.0.1
2001:db8:3c4d:15::1a2f:1a2b/96	2001:db8:3c4d:15::/96

broadcast 函数

计算地址所属网络的广播地址。

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;

cidr	broadcast
192.168.1.5/24	192.168.1.255/24
127.0.0.1	127.0.0.1
2001:db8:3c4d:15::1a2f:1a2b/96	2001:db8:3c4d:15::ffff:ffff/96

<<= 谓词

子网是否被另一个子网包含或与其相等？

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;

cidr	subnet_contained
192.168.1.0/24	true
127.0.0.1	false
2001:db8:3c4d:15::1a2f:1a2b/96	false

>>= 谓词

子网是否包含另一个子网或与其相等？

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;

cidr	subnet_contains
192.168.1.0/24	true
127.0.0.1	false
2001:db8:3c4d:15::1a2f:1a2b/96	false

HTML 转义与反转义函数

SELECT html_escape('&');

┌──────────────────┐
│ html_escape('&') │
│     varchar      │
├──────────────────┤
│ &            │
└──────────────────┘

SELECT html_unescape('&');

┌────────────────────────┐
│ html_unescape('&') │
│        varchar         │
├────────────────────────┤
│ &                      │
└────────────────────────┘