Postgres 9.11 Network Address type functions and operators

Source: Internet
Author: User

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 ' ' < inet ' '
<= Less than or equal to inet ' ' <= inet ' '
= Equals inet ' ' = inet ' '
>= Greater than or equal to inet ' ' >= inet ' '
> Greater than inet ' ' > inet ' '
<> Not equal to inet ' ' <> inet ' '
<< Included in inet ' ' << 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 ' '
>>= 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 (' ')
Host (inet) Text Extract the host address type as text Host (' ')
Masklen (inet) Int Extract Network Mask length Masklen (' ') 24
Set_masklen (inet,int) inet Set netmask length for inet values Set_masklen (' ', 16)
netmask (inet) inet To construct a network mask for a network Netmask (' ')
Hostmask (inet) inet To construct a host mask for a network Hostmask (' ')
Network (inet) Cidr Extracting the network portion of an address Network (' ')
text (inet) Text Extract IP address and mask length as text Text (inet ' ')
abbrev (inet) Text Extract abbreviations to display as text Abbrev (CIDR ' ') 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:: or:: ffff:


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.


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/24 192.168.0/24
192.168.1 192.168.1/24
192.168 192.168.0/24
128.1 128.1/16
128 128.0/16
128.1.2 128.1.2/24
10.1.2 10.1.2/24
10.1 10.1/16
10 10/8
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: :: ffff: :: ffff:1.2.3/120
:: ffff: :: ffff: :: ffff:

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 .


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.