| PostgreSQL 9.4.1 Documentation | |||
|---|---|---|---|
| Prev | Up | Chapter 8. Data Types | Next |
8.9. Network Address Types
PostgreSQL offers data types to store IPv4, IPv6, and MAC addresses, as shown inTable 8-21. It is better to use these types instead of plain text types to store network addresses, because these types offer input error checking and specialized operators and functions (seeSection 9.12).
Table 8-21. Network Address Types
| Name | Storage Size | Description |
|---|---|---|
| cidr | 7 or 19 bytes | IPv4 and IPv6 networks |
| inet | 7 or 19 bytes | IPv4 and IPv6 hosts and networks |
| macaddr | 6 bytes | MAC addresses |
When sortinginet orcidr data types, IPv4 addresses will always sort before IPv6 addresses, including IPv4 addresses encapsulated or mapped to IPv6 addresses, such as ::10.2.3.4 or ::ffff:10.4.3.2.
8.9.1.inet
Theinet type holds an IPv4 or IPv6 host address, and optionally its subnet, all in one field. The subnet is represented by the number of network address bits present in the host 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 only networks, you should use thecidr type rather thaninet.
The input format for this type isaddress/y whereaddress is an IPv4 or IPv6 address andy is the number of bits in the netmask. If the/y portion is missing, 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.
8.9.2.cidr
Thecidr type holds an IPv4 or IPv6 network specification. Input and output formats follow Classless Internet Domain Routing conventions. The format for specifying networks isaddress/y whereaddress is the network represented as an IPv4 or IPv6 address, andy is the number of bits in the netmask. Ify is omitted, it is calculated using assumptions from the older classful network numbering system, except 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-22 shows some examples.
Table 8-22.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.9.3.inet vs.cidr
The essential difference betweeninet andcidr data types is thatinet accepts values with nonzero bits to the right of the netmask, whereascidr does not.
Tip: If you do not like the output format forinet orcidr values, try the functions
host,text, andabbrev.
8.9.4.macaddr
Themacaddr type stores MAC addresses, known for example from Ethernet card hardware addresses (although MAC addresses are used for other purposes as well). Input is accepted in the following formats:
| '08:00:2b:01:02:03' |
| '08-00-2b-01-02-03' |
| '08002b:010203' |
| '08002b-010203' |
| '0800.2b01.0203' |
| '08002b010203' |
These examples would all specify the same address. Upper and lower case is accepted for the digitsa throughf. Output is always in the first of the forms shown.
IEEE Std 802-2001 specifies the second shown form (with hyphens) as the canonical form for MAC addresses, and specifies the first form (with colons) as the bit-reversed notation, so that 08-00-2b-01-02-03 = 01:00:4D:08:04:0C. This convention is widely ignored nowadays, and it is relevant only for obsolete network protocols (such as Token Ring). PostgreSQL makes no provisions for bit reversal, and all accepted formats use the canonical LSB order.
The remaining four input formats are not part of any standard.