PostgreSQL offers data types to store IPv4, IPv6, and MAC addresses, as shown in Table 8.17, “Network Address Types”. It is preferable to use these types instead of plain text types to store network addresses, because these types offer input error checking and several specialized operators and functions (see Section 9.11, “Network Address Functions and Operators”).
Table 8.17. Network Address Types
Name | Storage Size | Description |
---|---|---|
cidr |
12 or 24 bytes | IPv4 and IPv6 networks |
inet |
12 or 24 bytes | IPv4 and IPv6 hosts and networks |
macaddr |
6 bytes | MAC addresses |
When sorting inet
or cidr
data types,
IPv4 addresses will always sort before IPv6 addresses, including
IPv4 addresses encapsulated or mapped into IPv6 addresses, such as
::10.2.3.4 or ::ffff::10.4.3.2.
The inet
type holds an IPv4 or IPv6 host address, and
optionally the identity of the subnet it is in, all in one field.
The subnet identity is represented by stating how many bits of
the host address represent the network address (the
“netmask”). If the netmask is 32 and the address is IPv4,
then the value does not indicate a subnet, only a single host.
In IPv6, the address length is 128 bits, so 128 bits specify a
unique host address. 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
address/y
where
address
is an IPv4 or IPv6 address and
y
is the number of bits in the netmask. If the
/y
part is left off, then the
netmask is 32 for IPv4 and 128 for IPv6, so the value represents
just a single host. On display, the
/y
portion is 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 is address/y
where address
is the network represented as an
IPv4 or IPv6 address, and y
is the number of bits in the netmask. If
y
is omitted, it is calculated
using assumptions from the older classful network numbering system, except
that it will be at least large enough to include all of the octets
written in the input. It is an error to specify a network address
that has bits set to the right of the specified netmask.
Table 8.18, “cidr
Type Input Examples” shows some examples.
Table 8.18. cidr
Type Input Examples
cidr Input |
cidr Output |
|
---|---|---|
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 |
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.
If you do not 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 are used for
other 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' |
which would all specify the same
address. Upper and lower case is accepted for the digits
a
through f
. Output is always in the
last of the forms shown.