PostgreSQL 9.4.1 Documentation | |||
---|---|---|---|
Prev | Up | Chapter 9. Functions and Operators | Next |
9.12. Network Address Functions and Operators
Table 9-34 shows the operators available for thecidr andinet 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-34.cidr andinet Operators
Operator | Description | Example |
---|---|---|
< | is less than | inet '192.168.1.5' < inet '192.168.1.6' |
<= | is less than or equal | inet '192.168.1.5' <= inet '192.168.1.5' |
= | equals | inet '192.168.1.5' = inet '192.168.1.5' |
>= | is greater or equal | inet '192.168.1.5' >= inet '192.168.1.5' |
> | is greater than | inet '192.168.1.5' > inet '192.168.1.4' |
<> | is not equal | inet '192.168.1.5' <> inet '192.168.1.4' |
<< | is contained by | inet '192.168.1.5' << inet '192.168.1/24' |
<<= | is contained by or equals | inet '192.168.1/24' <<= inet '192.168.1/24' |
>> | contains | inet '192.168.1/24' >> inet '192.168.1.5' |
>>= | contains or equals | inet '192.168.1/24' >>= inet '192.168.1/24' |
&& | contains or is contained by | inet '192.168.1/24' && inet '192.168.1.80/28' |
~ | bitwise NOT | ~ inet '192.168.1.6' |
& | bitwise AND | inet '192.168.1.6' & inet '0.0.0.255' |
| | bitwise OR | inet '192.168.1.6' | inet '0.0.0.255' |
+ | addition | inet '192.168.1.6' + 25 |
- | subtraction | inet '192.168.1.43' - 36 |
- | subtraction | inet '192.168.1.43' - inet '192.168.1.19' |
Table 9-35 shows the functions available for use with thecidr andinet types. Theabbrev
,host
, andtext
functions are primarily intended to offer alternative display formats.
Table 9-35.cidr andinet Functions
Function | Return Type | Description | Example | Result |
---|---|---|---|---|
abbrev(inet) | text | abbreviated display format as text | abbrev(inet '10.1.0.0/16') | 10.1.0.0/16 |
abbrev(cidr) | text | abbreviated display format as text | abbrev(cidr '10.1.0.0/16') | 10.1/16 |
broadcast(inet) | inet | broadcast address for network | broadcast('192.168.1.5/24') | 192.168.1.255/24 |
family(inet) | int | extract family of address;4 for IPv4,6 for IPv6 | family('::1') | 6 |
host(inet) | text | extract IP address as text | host('192.168.1.5/24') | 192.168.1.5 |
hostmask(inet) | inet | construct host mask for network | hostmask('192.168.23.20/30') | 0.0.0.3 |
masklen(inet) | int | extract netmask length | masklen('192.168.1.5/24') | 24 |
netmask(inet) | inet | construct netmask for network | netmask('192.168.1.5/24') | 255.255.255.0 |
network(inet) | cidr | extract network part of address | network('192.168.1.5/24') | 192.168.1.0/24 |
set_masklen(inet,int) | inet | set netmask length forinet value | set_masklen('192.168.1.5/24', 16) | 192.168.1.5/16 |
set_masklen(cidr,int) | cidr | set netmask length forcidr value | set_masklen('192.168.1.0/24'::cidr, 16) | 192.168.0.0/16 |
text(inet) | text | extract IP address and netmask length as text | text(inet '192.168.1.5') | 192.168.1.5/32 |
Anycidr value can be cast toinet implicitly or explicitly; therefore, the functions shown above as operating oninet also work oncidr values. (Where there are separate functions forinet andcidr, it is because the behavior should be different for the two cases.) Also, it is permitted to cast aninet value tocidr. When this is done, any bits to the right of the netmask are silently zeroed to create a validcidr value. In addition, you can cast a text value toinet orcidr using normal casting syntax: for example,inet(expression) orcolname::cidr.
Table 9-36 shows the functions available for use with themacaddr type. The functiontrunc(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-36.macaddr Functions
Function | Return Type | Description | Example | Result |
---|---|---|---|---|
trunc(macaddr) | macaddr | set last 3 bytes to zero | trunc(macaddr '12:34:56:78:90:ab') | 12:34:56:00:00:00 |
Themacaddr type also supports the standard relational operators (>,<=, etc.) for lexicographical ordering, and the bitwise arithmetic operators (~,& and|) for NOT, AND and OR.