Postgres 9.11 Network Address type functions and operators

9.11. Network address type functions and operators

Table 9-31 shows the operators that can be used for CIDR and inet . Operators <<,<<= >>, and >>= are used to compute subnet inclusions: They only consider the network portion of two addresses, ignoring any host part, Then determine whether one of the network parts is equal to another or another subnet.

Table 9-31. CIDR and inet operators

operator	Description	Example
<	Less than?	inet ' 192.168.1.5 ' < inet ' 192.168.1.6 '
<=	Less than or equal to	inet ' 192.168.1.5 ' <= inet ' 192.168.1.5 '
=	Equals	inet ' 192.168.1.5 ' = inet ' 192.168.1.5 '
>=	Greater than or equal to	inet ' 192.168.1.5 ' >= inet ' 192.168.1.5 '
>	Greater than	inet ' 192.168.1.5 ' > inet ' 192.168.1.4 '
<>	Not equal to	inet ' 192.168.1.5 ' <> inet ' 192.168.1.4 '
<<	Included in	inet ' 192.168.1.5 ' << inet ' 192.168.1/24 '
<<=	contained in or equal to	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 '

Table 9-32 shows all functions that can be used for CIDR and inet . The function host,text , and abbrev are primarily designed to provide an optional display format. You can convert the syntax with a normal type: inet (expression) or colname:: inet Converts a text number field to inet.

Table 9-32. CIDR and inet functions

function	return type	Description	Example	Results
Broadcast (inet)	inet	Network broadcast Address	Broadcast (' 192.168.1.5/24 ')	192.168.1.255/24
Host (inet)	Text	Extract the host address type as text	Host (' 192.168.1.5/24 ')	192.168.1.5
Masklen (inet)	Int	Extract Network Mask length	Masklen (' 192.168.1.5/24 ')	24
Set_masklen (inet,int)	inet	Set netmask length for inet values	Set_masklen (' 192.168.1.5/24 ', 16)	192.168.1.5/16
netmask (inet)	inet	To construct a network mask for a network	Netmask (' 192.168.1.5/24 ')	255.255.255.0
Hostmask (inet)	inet	To construct a host mask for a network	Hostmask (' 192.168.23.20/30 ')	0.0.0.3
Network (inet)	Cidr	Extracting the network portion of an address	Network (' 192.168.1.5/24 ')	192.168.1.0/24
text (inet)	Text	Extract IP address and mask length as text	Text (inet ' 192.168.1.5 ')	192.168.1.5/32
abbrev (inet)	Text	Extract abbreviations to display as text	Abbrev (CIDR ' 10.1.0.0/16 ')	10.1/16
Family (inet)	Int	Extract Address family 4 for IPV4, 6 for IPV6	Family (':: 1 ')	6

Table 9-33 shows the functions that can be used with the macaddr type. The function trunc(macaddr) returns a MAC address with the last three bytes of the address set to zero. This will associate the remaining prefixes with a manufacturer. The source program directory Contrib/mac has some tools for creating and maintaining such association tables.

Table 9-33. macaddr function

function	return type	Description	Example	Results
trunc (macaddr)	Macaddr	Set the second three bytes to zero	Trunc (macaddr ' 12:34:56:78:90:ab ')	12:34:56:00:00:00

The macaddr type also supports standard relational operators (>,<=, etc.) for lexical sorting.

8.8. Network Address Types

PostgreSQL offers data types to the 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 Inpu T error checking and several specialized operators and functions (see section 9.11).

Table 8-17. Network Address Types

Name	Storage Size	Description
Cidr	or bytes	IPv4 and IPV6 Networks
inet	or bytes	IPv4 and IPV6 hosts and networks
Macaddr	6 bytes	MAC addresses

When sorting inet or CIDR data types, IPV4 addresses would always sort before IPv6 addresses, including I PV4 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's in, all in one Field. The subnet identity is represented by stating how many bits of the host address represent the network address ( the "NE Tmask "). If the netmask is a + and the address is IPv4, then the value does not indicate a subnet, with a single host. In IPv6, the address is a-bits, so-specify a unique host address. Note 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 a IPv4 or IPv6 address and y is t He number of bits in the netmask. If the /y part was left off, then the netmask is a + for IPv4 and IPv6, so the value represents just a sing Le host. On display, the /y portion are 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 are address/y where address is the network represented as an IPV4 or I Pv6 address, and y are the number of bits in the netmask. If y is omitted, it's calculated using assumptions from the older classful network numbering system, except that It'll is at least large enough to include all of the octets written in the input. It is an error to specify a network address, which 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.inetVs.CIDR

The essential difference between inet and CIDR data types is that inet accepts values with Nonz Ero bits to the right of the netmask, whereas CIDR does not.

Tip: If you don't like the output format for inet or CIDR values, try the functions host , text and abbrev .

8.8.4.macaddr

The macaddr type stores MAC addresses, i.e., Ethernet card hardware addresses (although MAC addresses is used For and 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 '

Postgres 9.11 Network Address type functions and operators