Postgresql 中文操作指南

8.9. Network Address Types #

PostgreSQL 提供了用于存储 IPv4、IPv6 和 MAC 地址的数据类型，如 Table 8.21 所示。最好使用这些类型而不是纯文本类型来存储网络地址，因为这些类型提供输入错误检查和专门的运算符和函数（见 Section 9.12）。

PostgreSQL offers data types to store IPv4, IPv6, and MAC addresses, as shown in Table 8.21. It is better to use these types instead of plain text types to store network addresses, because these types offer input error checking and specialized operators and functions (see Section 9.12).

Table 8.21. Network Address Types

Name

Storage Size

Description

cidr

7 or 19 bytes

IPv4 and IPv6 networks

inet

7 or 19 bytes

IPv4 and IPv6 hosts and networks

macaddr

6 bytes

MAC addresses

macaddr8

8 bytes

MAC addresses (EUI-64 format)

对 inet 或 cidr 数据类型进行排序时，IPv4 地址将始终在 IPv6 地址之前进行排序，包括封装或映射到 IPv6 地址的 IPv4 地址，例如：::10.2.3.4 或 ::ffff:10.4.3.2。

When sorting inet or cidr data types, IPv4 addresses will always sort before IPv6 addresses, including IPv4 addresses encapsulated or mapped to IPv6 addresses, such as ::10.2.3.4 or ::ffff:10.4.3.2.

8.9.1. inet #

inet 类型在单个字段中保存 IPv4 或 IPv6 主机地址以及可选的子网。子网通过主机地址中存在的网络地址位数（“网络掩码”）来表示。如果网络掩码为 32 且地址为 IPv4，则该值不表示子网，只表示单个主机。在 IPv6 中，地址长度为 128 位，因此 128 位指定了唯一的地址。请注意，如果您只想要接受网络，则应使用 cidr 类型，而不是 inet。

The inet type holds an IPv4 or IPv6 host address, and optionally its subnet, all in one field. The subnet is represented by the number of network address bits present in the host address (the “netmask”). If the netmask is 32 and the address is IPv4, then the value does not indicate a subnet, only a single host. In IPv6, the address length is 128 bits, so 128 bits specify a unique host address. Note that if you want to accept only networks, you should use the cidr type rather than inet.

此类型的输入格式为 address/y，其中 address 是一个 IPv4 或 IPv6 地址，y 是网络掩码中的位数。如果省略 /y 部分，则将网络掩码视为 IPv4 的 32 或 IPv6 的 128，因此该值只表示一个主机。在显示时，如果网络掩码指定了一个主机，则将隐藏 /y 部分。

The input format for this type is address/y where address is an IPv4 or IPv6 address and y is the number of bits in the netmask. If the /y portion is omitted, the netmask is taken to be 32 for IPv4 or 128 for IPv6, so the value represents just a single host. On display, the /y portion is suppressed if the netmask specifies a single host.

8.9.2. cidr #

cidr 类型会保存 IPv4 或 IPv6 网络规范。输入和输出格式遵循无类别域间路由约定。指定网络的格式为 address/y ，其中 address 是作为 IPv4 或 IPv6 地址表示的网络的最低地址， y 是网络掩码中的比特位数。如果省略 y ，则它会根据旧类间网络编号系统的假设进行计算，但它至少会大到足以包含输入中写入的所有八位组。指定网络地址错误，其中比特位设置在指定的网络掩码的右侧。

The cidr type holds an IPv4 or IPv6 network specification. Input and output formats follow Classless Internet Domain Routing conventions. The format for specifying networks is address/y where address is the network’s lowest address represented as an IPv4 or IPv6 address, and y is the number of bits in the netmask. If y is omitted, it is calculated using assumptions from the older classful network numbering system, except it will be at least large enough to include all of the octets written in the input. It is an error to specify a network address that has bits set to the right of the specified netmask.

Table 8.22 显示了一些示例。

Table 8.22 shows some examples.

Table 8.22. cidr Type Input Examples

cidr Input

cidr Output

abbrev(_cidr)_

192.168.100.128/25

192.168/24

192.168.0.0/24

192.168.0/24

192.168/25

192.168.0.0/25

192.168.1

192.168.1.0/24

192.168.1/24

192.168

192.168.0.0/24

192.168.0/24

128.1

128.1.0.0/16

128.1/16

128

128.0.0.0/16

128.0/16

128.1.2

128.1.2.0/24

128.1.2/24

10.1.2

10.1.2.0/24

10.1.2/24

10.1

10.1.0.0/16

10.1/16

10.0.0.0/8

10/8

10.1.2.3/32

2001:4f8:3:ba::/64

2001:4f8:3:ba/64

2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128

::ffff:1.2.3.0/120

::ffff:1.2.3/120

::ffff:1.2.3.0/128

8.9.3. inet vs. cidr #

inet 和 cidr 数据类型之间的本质区别在于， inet 接受网络掩码右侧为非零比特位的数值，而 cidr 则不接受。例如， 192.168.0.1/24 对 inet 有效，但对 cidr 无效。

The essential difference between inet and cidr data types is that inet accepts values with nonzero bits to the right of the netmask, whereas cidr does not. For example, 192.168.0.1/24 is valid for inet but not for cidr.

Tip

如果您不喜欢 inet 或 cidr 值的输出格式，请尝试 host、text 和 abbrev 函数。

If you do not like the output format for inet or cidr values, try the functions host, text, and abbrev.

8.9.4. macaddr #

macaddr 类型存储 MAC 地址，例如从以太网卡硬件地址得知的（尽管 MAC 地址也用于其他用途）。输入的下列格式均可接受：

The macaddr type stores MAC addresses, known for example from Ethernet card hardware addresses (although MAC addresses are used for other purposes as well). Input is accepted in the following formats:

所有这些示例都指定相同的地址。a 至 f 的数字接受大小写形式。输出始终采用显示的第一种形式。

These examples all specify the same address. Upper and lower case is accepted for the digits a through f. Output is always in the first of the forms shown.

IEEE 标准 802-2001 将显示的第二种形式（带连字符）指定为 MAC 地址的规范形式，并将第一种形式（带冒号）指定为用于按比特反转、MSB 优先的表示法，以便 08-00-2b-01-02-03 = 10:00:D4:80:40:C0。如今这个约定被广泛忽略，它仅与过时的网络协议（例如令牌环）相关。PostgreSQL 不会为比特反转做出任何规定；所有接受的格式都使用规则 LSB 顺序。

IEEE Standard 802-2001 specifies the second form shown (with hyphens) as the canonical form for MAC addresses, and specifies the first form (with colons) as used with bit-reversed, MSB-first notation, so that 08-00-2b-01-02-03 = 10:00:D4:80:40:C0. This convention is widely ignored nowadays, and it is relevant only for obsolete network protocols (such as Token Ring). PostgreSQL makes no provisions for bit reversal; all accepted formats use the canonical LSB order.

其余五种输入格式不属于任何标准。

The remaining five input formats are not part of any standard.

8.9.5. macaddr8 #

macaddr8 类型以 EUI-64 格式存储 MAC 地址，例如从以太网卡硬件地址得知的（尽管 MAC 地址也用于其他用途）。此类型既可以接受 6 字节长度的 MAC 地址，也可以接受 8 字节长度的 MAC 地址，并以 8 字节长度的格式存储这些地址。以 6 字节格式给出的 MAC 地址将以 8 字节长度的格式存储，其中第 4 和第 5 字节分别设置为 FF 和 FE。注意，IPv6 使用经修改的 EUI-64 格式，其中从 EUI-48 转换后应将第 7 位设置为 1。提供 macaddr8_set7bit 函数来进行此更改。一般而言，任何由成对十六进制数字（在字节边界上）组成、可选地始终由 ':'、'-' 或 '.' 之一分隔的输入都可接受。十六进制数字的数目必须为 16 (8 字节) 或 12 (6 字节)。将忽略前导和尾随空格。以下是可接受输入格式的示例：

The macaddr8 type stores MAC addresses in EUI-64 format, known for example from Ethernet card hardware addresses (although MAC addresses are used for other purposes as well). This type can accept both 6 and 8 byte length MAC addresses and stores them in 8 byte length format. MAC addresses given in 6 byte format will be stored in 8 byte length format with the 4th and 5th bytes set to FF and FE, respectively. Note that IPv6 uses a modified EUI-64 format where the 7th bit should be set to one after the conversion from EUI-48. The function macaddr8_set7bit is provided to make this change. Generally speaking, any input which is comprised of pairs of hex digits (on byte boundaries), optionally separated consistently by one of ':', '-' or '.', is accepted. The number of hex digits must be either 16 (8 bytes) or 12 (6 bytes). Leading and trailing whitespace is ignored. The following are examples of input formats that are accepted:

所有这些示例都指定相同的地址。a 至 f 的数字接受大小写形式。输出始终采用显示的第一种形式。

These examples all specify the same address. Upper and lower case is accepted for the digits a through f. Output is always in the first of the forms shown.

上面显示的后六种输入格式不属于任何标准。

The last six input formats shown above are not part of any standard.

若要将 EUI-48 格式的传统 48 位 MAC 地址转换为修改的 EUI-64 格式，以包含在 IPv6 地址的主机部分中，请如下所示使用 macaddr8_set7bit：

To convert a traditional 48 bit MAC address in EUI-48 format to modified EUI-64 format to be included as the host portion of an IPv6 address, use macaddr8_set7bit as shown:

SELECT macaddr8_set7bit('08:00:2b:01:02:03');

    macaddr8_set7bit
-------------------------
 0a:00:2b:ff:fe:01:02:03
(1 row)