PostgreSQL offers data types to store IP and MAC
addresses, shown in Table 5-18. It
is preferable to use these types over plain text types, because
these types offer input error checking and several specialized
operators and functions.
Table 5-18. Network Address Data Types
|valid IPv4 networks
|IP hosts and networks
|valid IPv4 hosts or networks
IPv6 is not yet supported.
The inet type holds an IP host address, and
optionally the identity of the subnet it is in, all in one field.
The subnet identity is represented by the number of bits in the
network part of the address (the "netmask"). If the
netmask is 32,
then the value does not indicate a subnet, only a single host.
Note that if you want to accept networks only, you should use the
cidr type rather than inet.
The input format for this type is x.x.x.x/y where x.x.x.x is an IP address and
y is the number of
bits in the netmask. If the /y part is left off, then the
netmask is 32, and the value represents just a single host.
On display, the /y
portion is suppressed if the netmask is 32.
The cidr type holds an IP network specification.
Input and output formats follow Classless Internet Domain Routing
The format for
specifying classless networks is x.x.x.x/y where x.x.x.x is the network and y is the number of bits in the netmask. If
y is omitted, it is calculated
using assumptions from the older classful numbering system, except
that it will be at least large enough to include all of the octets
written in the input.
Table 5-19 shows some examples.
Table 5-19. cidr Type Input Examples
The essential difference between inet and cidr
data types is that inet accepts values with nonzero bits to
the right of the netmask, whereas cidr does not.
Tip: If you do not like the output format for inet or
cidr values, try the host(),
text(), and abbrev() functions.
The macaddr type stores MAC addresses, i.e., Ethernet
card hardware addresses (although MAC addresses are used for
other purposes as well). Input is accepted in various customary
which would all specify the same
address. Upper and lower case is accepted for the digits
. Output is always in the
last of the shown forms.
The directory contrib/mac
in the PostgreSQL source distribution
contains tools that can be used to map MAC addresses to hardware