Postgresql 中文操作指南
9.12. Network Address Functions and Operators #
IP 网络地址类型 _cidr_和 _inet_支持 Table 9.1中显示的常用比较运算符，以及 Table 9.39和 Table 9.40中显示的专门运算符和函数。
The IP network address types, cidr and inet, support the usual comparison operators shown in Table 9.1 as well as the specialized operators and functions shown in Table 9.39 and Table 9.40.
任何 cidr 值都可以隐式转换为 inet；因此，下面显示为对 inet 执行操作的运算符和函数也适用于 cidr 值。（在针对 inet 和 cidr 有单独的函数的情况下，这意味着这两种情况下的行为应该是不同的。）此外，允许将 inet 值转换为 cidr。执行此操作时，掩码右侧的任何位都会被静默清零以创建有效的 cidr 值。
Any cidr value can be cast to inet implicitly; therefore, the operators and functions shown below as operating on inet also work on cidr values. (Where there are separate functions for inet and cidr, it is because the behavior should be different for the two cases.) Also, it is permitted to cast an inet value to cidr. When this is done, any bits to the right of the netmask are silently zeroed to create a valid cidr value.
Table 9.39. IP Address Operators
Operator Description Example(s) |
inet << inet → boolean Is subnet strictly contained by subnet? This operator, and the next four, test for subnet inclusion. They consider only the network parts of the two addresses (ignoring any bits to the right of the netmasks) and determine whether one network is identical to or a subnet of the other. inet '192.168.1.5' << inet '192.168.1/24' → t inet '192.168.0.5' << inet '192.168.1/24' → f inet '192.168.1/24' << inet '192.168.1/24' → f |
inet <⇐ inet → boolean Is subnet contained by or equal to subnet? inet '192.168.1/24' <⇐ inet '192.168.1/24' → t |
inet >> inet → boolean Does subnet strictly contain subnet? inet '192.168.1/24' >> inet '192.168.1.5' → t |
inet >>= inet → boolean Does subnet contain or equal subnet? inet '192.168.1/24' >>= inet '192.168.1/24' → t |
inet && inet → boolean Does either subnet contain or equal the other? inet '192.168.1/24' && inet '192.168.1.80/28' → t inet '192.168.1/24' && inet '192.168.2.0/28' → f |
~ inet → inet Computes bitwise NOT. ~ inet '192.168.1.6' → 63.87.254.249 |
inet & inet → inet Computes bitwise AND. inet '192.168.1.6' & inet '0.0.0.255' → 0.0.0.6 |
inet _ |
_ inet → inet Computes bitwise OR. _inet '192.168.1.6' |
inet '0.0.0.255'_ → 192.168.1.255 |
inet + bigint → inet Adds an offset to an address. inet '192.168.1.6' + 25 → 192.168.1.31 |
bigint + inet → inet Adds an offset to an address. 200 + inet '::ffff:fff0:1' → ::ffff:255.240.0.201 |
inet - bigint → inet Subtracts an offset from an address. inet '192.168.1.43' - 36 → 192.168.1.7 |
inet - inet → bigint Computes the difference of two addresses. inet '192.168.1.43' - inet '192.168.1.19' → 24 inet '::1' - inet '::ffff:1' → -4294901760 |
Table 9.40. IP Address Functions
Function Description Example(s) |
abbrev ( inet ) → text Creates an abbreviated display format as text. (The result is the same as the inet output function produces; it is “abbreviated” only in comparison to the result of an explicit cast to text, which for historical reasons will never suppress the netmask part.) abbrev(inet '10.1.0.0/32') → 10.1.0.0 |
abbrev ( cidr ) → text Creates an abbreviated display format as text. (The abbreviation consists of dropping all-zero octets to the right of the netmask; more examples are in Table 8.22.) abbrev(cidr '10.1.0.0/16') → 10.1/16 |
broadcast ( inet ) → inet Computes the broadcast address for the address’s network. broadcast(inet '192.168.1.5/24') → 192.168.1.255/24 |
family ( inet ) → integer Returns the address’s family: 4 for IPv4, 6 for IPv6. family(inet '::1') → 6 |
host ( inet ) → text Returns the IP address as text, ignoring the netmask. host(inet '192.168.1.0/24') → 192.168.1.0 |
hostmask ( inet ) → inet Computes the host mask for the address’s network. hostmask(inet '192.168.23.20/30') → 0.0.0.3 |
inet_merge ( inet, inet ) → cidr Computes the smallest network that includes both of the given networks. inet_merge(inet '192.168.1.5/24', inet '192.168.2.5/24') → 192.168.0.0/22 |
inet_same_family ( inet, inet ) → boolean Tests whether the addresses belong to the same IP family. inet_same_family(inet '192.168.1.5/24', inet '::1') → f |
masklen ( inet ) → integer Returns the netmask length in bits. masklen(inet '192.168.1.5/24') → 24 |
netmask ( inet ) → inet Computes the network mask for the address’s network. netmask(inet '192.168.1.5/24') → 255.255.255.0 |
network ( inet ) → cidr Returns the network part of the address, zeroing out whatever is to the right of the netmask. (This is equivalent to casting the value to cidr.) network(inet '192.168.1.5/24') → 192.168.1.0/24 |
set_masklen ( inet, integer ) → inet Sets the netmask length for an inet value. The address part does not change. set_masklen(inet '192.168.1.5/24', 16) → 192.168.1.5/16 |
set_masklen ( cidr, integer ) → cidr Sets the netmask length for a cidr value. Address bits to the right of the new netmask are set to zero. set_masklen(cidr '192.168.1.0/24', 16) → 192.168.0.0/16 |
text ( inet ) → text Returns the unabbreviated IP address and netmask length as text. (This has the same result as an explicit cast to text.) text(inet '192.168.1.5') → 192.168.1.5/32 |
Tip
abbrev、host 和 text 函数主要用于为 IP 地址提供备用显示格式。
The abbrev, host, and text functions are primarily intended to offer alternative display formats for IP addresses.
MAC 地址类型 macaddr_和 _macaddr8_支持 Table 9.1中显示的常用比较运算符，以及 Table 9.41中显示的专门函数。此外，它们支持按位逻辑运算符 _~、&_和 _|（非、且和或），就像上面针对 IP 地址显示的一样。
The MAC address types, macaddr and macaddr8, support the usual comparison operators shown in Table 9.1 as well as the specialized functions shown in Table 9.41. In addition, they support the bitwise logical operators ~, & and | (NOT, AND and OR), just as shown above for IP addresses.
Table 9.41. MAC Address Functions
Function Description Example(s) |
trunc ( macaddr ) → macaddr Sets the last 3 bytes of the address to zero. The remaining prefix can be associated with a particular manufacturer (using data not included in PostgreSQL). trunc(macaddr '12:34:56:78:90:ab') → 12:34:56:00:00:00 |
trunc ( macaddr8 ) → macaddr8 Sets the last 5 bytes of the address to zero. The remaining prefix can be associated with a particular manufacturer (using data not included in PostgreSQL). trunc(macaddr8 '12:34:56:78:90:ab:cd:ef') → 12:34:56:00:00:00:00:00 |
macaddr8_set7bit ( macaddr8 ) → macaddr8 Sets the 7th bit of the address to one, creating what is known as modified EUI-64, for inclusion in an IPv6 address. macaddr8_set7bit(macaddr8 '00:34:56:ab:cd:ef') → 02:34:56:ff:fe:ab:cd:ef |