9.12. Network Address Functions and Operators

Note: The following description applies both to Postgres-XC and PostgreSQL if not described explicitly. You can read PostgreSQL as Postgres-XC except for version number, which is specific to each product.

Table 9-33 shows the operators available for the cidr and inet types. The operators <<, <<=, >>, and >>= test for subnet inclusion. They consider only the network parts of the two addresses (ignoring any host part) and determine whether one network is identical to or a subnet of the other.

Table 9-33. cidr and inet Operators

OperatorDescriptionExample
< is less thaninet '192.168.1.5' < inet '192.168.1.6'
<= is less than or equalinet '192.168.1.5' <= inet '192.168.1.5'
= equalsinet '192.168.1.5' = inet '192.168.1.5'
>= is greater or equalinet '192.168.1.5' >= inet '192.168.1.5'
> is greater thaninet '192.168.1.5' > inet '192.168.1.4'
<> is not equalinet '192.168.1.5' <> inet '192.168.1.4'
<< is contained withininet '192.168.1.5' << inet '192.168.1/24'
<<= is contained within or equalsinet '192.168.1/24' <<= inet '192.168.1/24'
>> containsinet '192.168.1/24' >> inet '192.168.1.5'
>>= contains or equalsinet '192.168.1/24' >>= inet '192.168.1/24'
~ bitwise NOT~ inet '192.168.1.6'
& bitwise ANDinet '192.168.1.6' & inet '0.0.0.255'
| bitwise ORinet '192.168.1.6' | inet '0.0.0.255'
+ additioninet '192.168.1.6' + 25
- subtractioninet '192.168.1.43' - 36
- subtractioninet '192.168.1.43' - inet '192.168.1.19'

Table 9-34 shows the functions available for use with the cidr and inet types. The abbrev, host, and text functions are primarily intended to offer alternative display formats.

Table 9-34. cidr and inet Functions

FunctionReturn TypeDescriptionExampleResult
abbrev(inet) textabbreviated display format as textabbrev(inet '10.1.0.0/16')10.1.0.0/16
abbrev(cidr)textabbreviated display format as textabbrev(cidr '10.1.0.0/16')10.1/16
broadcast(inet) inetbroadcast address for networkbroadcast('192.168.1.5/24')192.168.1.255/24
family(inet) intextract family of address; 4 for IPv4, 6 for IPv6family('::1')6
host(inet) textextract IP address as texthost('192.168.1.5/24')192.168.1.5
hostmask(inet) inetconstruct host mask for networkhostmask('192.168.23.20/30')0.0.0.3
masklen(inet) intextract netmask lengthmasklen('192.168.1.5/24')24
netmask(inet) inetconstruct netmask for networknetmask('192.168.1.5/24')255.255.255.0
network(inet) cidrextract network part of addressnetwork('192.168.1.5/24')192.168.1.0/24
set_masklen(inet, int) inetset netmask length for inet valueset_masklen('192.168.1.5/24', 16)192.168.1.5/16
set_masklen(cidr, int)cidrset netmask length for cidr valueset_masklen('192.168.1.0/24'::cidr, 16)192.168.0.0/16
text(inet) textextract IP address and netmask length as texttext(inet '192.168.1.5')192.168.1.5/32

Any cidr value can be cast to inet implicitly or explicitly; therefore, the functions shown above 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. In addition, you can cast a text value to inet or cidr using normal casting syntax: for example, inet(expression) or colname::cidr.

Table 9-35 shows the functions available for use with the macaddr type. The function trunc(macaddr) returns a MAC address with the last 3 bytes set to zero. This can be used to associate the remaining prefix with a manufacturer.

Table 9-35. macaddr Functions

FunctionReturn TypeDescriptionExampleResult
trunc(macaddr) macaddrset last 3 bytes to zerotrunc(macaddr '12:34:56:78:90:ab')12:34:56:00:00:00

The macaddr type also supports the standard relational operators (>, <=, etc.) for lexicographical ordering, and the bitwise arithmetic operators (~, & and |) for NOT, AND and OR.