Network Address Data Types

PostgreSQL supports three data types that are designed to hold network addresses, both IP[8] (logical) and MAC[9] (physical) addresses. I don't think there are many applications that require the storage of an IP or MAC address, so I won't spend too much time describing them. The PostgreSQL User's Guide contains all the details that you might need to know regarding network data types.

[8] IP stands for Internet Protocol, the substrate of the Internet.

[9] The acronym MAC stands for one or more of the following: Machine Address Code, Media Access Control, or Macaroni And Cheese.

MACADDR

The MACADDR type is designed to hold a MAC address. A MAC address is a hardware address, usually the address of an ethernet interface.

CIDR

The CIDR data type is designed to hold an IP network address. A CIDR value contains an IP network address and an optional netmask (the netmask determines the number of meaningful bits in the network address).

INET

An INET value can hold the IP address of a network or of a network host. An INET value contains a network address and an optional netmask. If the netmask is omitted, it is assumed that the address identifies a single host (in other words, there is no discernible network component in the address).

Note that an INET value can represent a network or a host, but a CIDR is designed to represent the address of a network.

Syntax for Literal Values

The syntax required for literal network values is shown in Table 2.27.

Starting with version 7.4, you can also store IPv6 (colon-separated) addresses in an INET or CIDR value.

An INET or CIDR value consumes either 12 bytes or 24 bytes of storage (depending on the number of bits in the address). A MACADDR value consumes 6 bytes of storage.

Supported Operators

PostgreSQL provides comparison operators that you can use to compare two INET values, two CIDR values, or two MACADDR values. The comparison operators work by first checking the common bits in the network components of the two addresses; then, if those are equal, the address with the greatest number of netmask bits is considered the largest value. If the number of bits in the netmask is equal (and the network components of the addresses are equal), then the entire address is compared. The net effect (pun intended) is that 192.168.0.22/24 is considered greater than 192.168.0.22/20.

When you are working with two INET (or CIDR) values, you can also check for containership. Table 2.28 describes the network address operators.