Postgres 9.11 网络地址类型函数和操作符
9.11. 网络地址类型函数和操作符
Table 9-31 显示了可以用于 cidr 和 inet 的操作符。 操作符 <<，<<= >>，和 >>= 用于计算子网包含：它们只考虑两个地址的网络部分，忽略任何主机部分， 然后判断其中一个网络部分是等于另外一个还是另外一个的子网。
Table 9-31. cidr 和 inet 操作符
| 操作符 | 描述 | 例子 |
|---|---|---|
| < | 小于吗 | inet '192.168.1.5' < inet '192.168.1.6' |
| <= | 小于或等于 | inet '192.168.1.5' <= inet '192.168.1.5' |
| = | 等于 | inet '192.168.1.5' = inet '192.168.1.5' |
| >= | 大于或等于 | inet '192.168.1.5' >= inet '192.168.1.5' |
| > | 大于 | inet '192.168.1.5' > inet '192.168.1.4' |
| <> | 不等于 | inet '192.168.1.5' <> inet '192.168.1.4' |
| << | 包含于 | inet '192.168.1.5' << inet '192.168.1/24' |
| <<= | 包含于或等于 | inet '192.168.1/24' <<= inet '192.168.1/24' |
| >> | 包含 | inet '192.168.1/24' >> inet '192.168.1.5' |
| >>= | 包含或等于 | inet '192.168.1/24' >>= inet '192.168.1/24' |
Table 9-32 显示了所有可以用于 cidr 和 inet 的函数。 函数 host，text 和 abbrev 主要是为了提供可选的显示格式用的。 你可以用普通的类型转换语法∶ inet(expression) 或者 colname::inet 把一个文本数域转换成inet。
Table 9-32. cidr 和 inet 函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| broadcast(inet) | inet | 网络广播地址 | broadcast('192.168.1.5/24') | 192.168.1.255/24 |
| host(inet) | text | 将主机地址类型抽出为文本 | host('192.168.1.5/24') | 192.168.1.5 |
| masklen(inet) | int | 抽取网络掩码长度 | masklen('192.168.1.5/24') | 24 |
| set_masklen(inet,int) | inet | 为inet数值设置网络掩码长度 | set_masklen('192.168.1.5/24',16) | 192.168.1.5/16 |
| netmask(inet) | inet | 为网络构造网络掩码 | netmask('192.168.1.5/24') | 255.255.255.0 |
| hostmask(inet) | inet | 为网络构造主机掩码 | hostmask('192.168.23.20/30') | 0.0.0.3 |
| network(inet) | cidr | 抽取地址的网络部分 | network('192.168.1.5/24') | 192.168.1.0/24 |
| text(inet) | text | 把 IP 地址和掩码长度抽取为文本 | text(inet '192.168.1.5') | 192.168.1.5/32 |
| abbrev(inet) | text | 抽取缩写显示为文本 | abbrev(cidr '10.1.0.0/16') | 10.1/16 |
| family(inet) | int | 抽取地址族 4 for IPv4, 6 for IPv6 | family('::1') | 6 |
Table 9-33 显示了可以用于 macaddr 类型的函数。 函数 trunc(macaddr) 返回一个 MAC 地址，该地址的最后三个字节设置为零。 这样可以把剩下的前缀与一个制造商相关联。 源程序目录 contrib/mac 里有一些用于创建和维护这样的 关联表的工具。
Table 9-33. macaddr 函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| trunc(macaddr) | macaddr | 把后三个字节置为零 | trunc(macaddr '12:34:56:78:90:ab') | 12:34:56:00:00:00 |
macaddr 类型还支持标准关系操作符 （>，<=，等） 用于词法排序。
8.8. Network Address Types
PostgreSQL offers data types to store IPv4, IPv6, and MAC addresses, as shown in Table 8-17. It is preferable to use these types instead of plain text types to store network addresses, because these types offer input error checking and several specialized operators and functions (see Section 9.11).
Table 8-17. Network Address Types
| Name | Storage Size | Description |
|---|---|---|
| cidr | 12 or 24 bytes | IPv4 and IPv6 networks |
| inet | 12 or 24 bytes | IPv4 and IPv6 hosts and networks |
| macaddr | 6 bytes | MAC addresses |
When sorting inet or cidr data types, IPv4 addresses will always sort before IPv6 addresses, including IPv4 addresses encapsulated or mapped into IPv6 addresses, such as ::10.2.3.4 or ::ffff:10.4.3.2.
8.8.1. inet
The inet type holds an IPv4 or IPv6 host address, and optionally the identity of the subnet it is in, all in one field. The subnet identity is represented by stating how many bits of the host address represent the network 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 networks only, you should use the cidr type rather than inet.
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 part is left off, then the netmask is 32 for IPv4 and 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.8.2. cidr
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 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 that 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-18 shows some examples.
Table 8-18. cidr Type Input Examples
| cidr Input | cidr Output | abbrev(cidr) |
|---|---|---|
| 192.168.100.128/25 | 192.168.100.128/25 | 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.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 | 10.0.0.0/8 | 10/8 |
| 10.1.2.3/32 | 10.1.2.3/32 | 10.1.2.3/32 |
| 2001:4f8:3:ba::/64 | 2001:4f8:3:ba::/64 | 2001:4f8:3:ba::/64 |
| 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128 | 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128 | 2001:4f8:3:ba:2e0:81ff:fe22:d1f1 |
| ::ffff:1.2.3.0/120 | ::ffff:1.2.3.0/120 | ::ffff:1.2.3/120 |
| ::ffff:1.2.3.0/128 | ::ffff:1.2.3.0/128 | ::ffff:1.2.3.0/128 |
8.8.3. inet vs. 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.
Tip: If you do not like the output format for inet or cidr values, try the functions
host,text, andabbrev.
8.8.4. macaddr
The macaddr type stores MAC addresses, i.e., Ethernet card hardware addresses (although MAC addresses are used for other purposes as well). Input is accepted in various customary formats, including
| '08002b:010203' |
| '08002b-010203' |
| '0800.2b01.0203' |
| '08-00-2b-01-02-03' |
| '08:00:2b:01:02:03' |
