- publishing free software manuals
PostgreSQL Reference Manual - Volume 1 - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
ISBN 0954612027
RRP £32.00 ($49.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

7.11 Network Address Functions and Operators

Table 7-31 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 part is identical to or a subnet of the other.

Table 7-31: cidr and inet Operators
Operator Description
< is less than
e.g. inet '192.168.1.5' < inet '192.168.1.6'
<= is less than or equal
e.g. inet '192.168.1.5' <= inet '192.168.1.5'
= equals
e.g. inet '192.168.1.5' = inet '192.168.1.5'
>= is greater or equal
e.g. inet '192.168.1.5' >= inet '192.168.1.5'
> is greater than
e.g. inet '192.168.1.5' > inet '192.168.1.4'
<> is not equal
e.g. inet '192.168.1.5' <> inet '192.168.1.4'
<< is contained within
e.g. inet '192.168.1.5' << inet '192.168.1/24'
<<= is contained within or equals
e.g. inet '192.168.1/24' <<= inet '192.168.1/24'
>> contains
e.g. inet '192.168.1/24' >> inet '192.168.1.5'
>>= contains or equals
e.g. inet '192.168.1/24' >>= inet '192.168.1/24'
~ bitwise NOT
e.g. ~ inet '192.168.1.6'
& bitwise AND
e.g. inet '192.168.1.6' & inet '0.0.0.255'
| bitwise OR
e.g. inet '192.168.1.6' | inet '0.0.0.255'
+ addition
e.g. inet '192.168.1.6' + 25
- subtraction
e.g. inet '192.168.1.43' - 36
- subtraction
e.g. inet '192.168.1.43' - inet '192.168.1.19'

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

Table 7-32: cidr and inet Functions
Function Return Type Description
abbrev(inet) text abbreviated display format as text
e.g. abbrev(inet '10.1.0.0/16') => 10.1.0.0/16
abbrev(cidr) text abbreviated display format as text
e.g. abbrev(cidr '10.1.0.0/16') => 10.1/16
broadcast(inet) inet broadcast address for network
e.g. broadcast('192.168.1.5/24') => 192.168.1.255/24
family(inet) int extract family of address; 4 for IPv4, 6 for IPv6
e.g. family('::1') => 6
host(inet) text extract IP address as text
e.g. host('192.168.1.5/24') => 192.168.1.5
hostmask(inet) inet construct host mask for network
e.g. hostmask('192.168.23.20/30') => 0.0.0.3
masklen(inet) int extract netmask length
e.g. masklen('192.168.1.5/24') => 24
netmask(inet) inet construct netmask for network
e.g. netmask('192.168.1.5/24') => 255.255.255.0
network(inet) cidr extract network part of address
e.g. network('192.168.1.5/24') => 192.168.1.0/24
set_masklen(inet, int) inet set netmask length for inet value
e.g. set_masklen('192.168.1.5/24', 16) => 192.168.1.5/16
set_masklen(cidr, int) cidr set netmask length for cidr value
e.g. 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
e.g. text(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 7-33 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 7-33: macaddr Functions
Function Return Type Description
trunc(macaddr) macaddr set last 3 bytes to zero
e.g. trunc(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.

ISBN 0954612027PostgreSQL Reference Manual - Volume 1 - SQL Language ReferenceSee the print edition