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