Network Address Types
GaussDB offers data types to store IPv4 and MAC addresses.
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 (see Network Address Functions and Operators).
Name |
Storage Space |
Description |
---|---|---|
cidr |
7 bytes |
IPv4 networks |
inet |
7 bytes |
IPv4 hosts and networks |
macaddr |
6 bytes |
MAC address |
cidr
The cidr type (Classless Inter-Domain Routing) holds an IPv4 network address. The format for specifying networks is address/y where address is the network represented as an IPv4 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 it will be at least large enough to include all of the octets written in the input.
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 |
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 |
Example:
openGauss=# CREATE TABLE cidr_test(id int, c cidr); CREATE TABLE openGauss=# INSERT INTO cidr_test VALUES (1, '192.168.100.128/25'); INSERT 0 1 openGauss=# INSERT INTO cidr_test VALUES (2, '192.168/24'); INSERT 0 1 openGauss=# INSERT INTO cidr_test VALUES (3, '192.168/25'); INSERT 0 1 openGauss=# INSERT INTO cidr_test VALUES (4, '192.168.1'); INSERT 0 1 openGauss=# INSERT INTO cidr_test VALUES (5, '192.168'); INSERT 0 1 openGauss=# INSERT INTO cidr_test VALUES (6, '10.1.2'); INSERT 0 1 openGauss=# INSERT INTO cidr_test VALUES (7, '10.1'); INSERT 0 1 openGauss=# INSERT INTO cidr_test VALUES (8, '10'); INSERT 0 1 openGauss=# INSERT INTO cidr_test VALUES (9, '2001:4f8:3:ba::/64'); INSERT 0 1 openGauss=# INSERT INTO cidr_test VALUES (10, '2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128'); INSERT 0 1 openGauss=# INSERT INTO cidr_test VALUES (11, '::ffff:127.0.0.0/120'); INSERT 0 1 openGauss=# INSERT INTO cidr_test VALUES (12, '::ffff:127.0.0.0/128'); INSERT 0 1 openGauss=# SELECT * FROM cidr_test ORDER BY id; id | c ----+-------------------------------------- 1 | 192.168.100.128/25 2 | 192.168.0.0/24 3 | 192.168.0.0/25 4 | 192.168.1.0/24 5 | 192.168.0.0/24 6 | 10.1.2.0/24 7 | 10.1.0.0/16 8 | 10.0.0.0/8 9 | 2001:4f8:3:ba::/64 10 | 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128 11 | ::ffff:127.0.0.0/120 12 | ::ffff:127.0.0.0/128 (12 rows) openGauss=# DROP TABLE cidr_test; DROP TABLE
inet
The inet type holds an IPv4 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 an IPv4 address, then the value does not indicate a subnet, only a single host.
The input format for this type is address/y where address is an IPv4 address and y is the number of bits in the netmask. If /y is omitted, the subnet mask is 32 for an IPv4 address, and the value represents just a single host. On display, the /y portion is suppressed if the netmask specifies a single host.
The essential difference between the inet and cidr data types is that inet accepts values with nonzero bits to the right of the netmask, whereas cidr does not.
Example:
openGauss=# CREATE TABLE inet_test(id int, i inet); CREATE TABLE openGauss=# INSERT INTO inet_test VALUES (1, '192.168.100.128/25'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (2, '192.168.100.128'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (3, '192.168.1.0/24'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (4, '192.168.1.0/25'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (5, '192.168.1.255/24'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (6, '192.168.1.255/25'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (7, '10.1.2.3/8'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (8, '11.1.2.3/16'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (9, '12.1.2.3/24'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (10, '13.1.2.3/32'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (11, '2001:4f8:3:ba::/64'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (12, '2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (13, '::ffff:127.0.0.0/120'); INSERT 0 1 openGauss=# INSERT INTO inet_test VALUES (14, '::ffff:127.0.0.0/128'); INSERT 0 1 openGauss=# SELECT * FROM inet_test ORDER BY id; id | i ----+---------------------------------- 1 | 192.168.100.128/25 2 | 192.168.100.128 3 | 192.168.1.0/24 4 | 192.168.1.0/25 5 | 192.168.1.255/24 6 | 192.168.1.255/25 7 | 10.1.2.3/8 8 | 11.1.2.3/16 9 | 12.1.2.3/24 10 | 13.1.2.3 11 | 2001:4f8:3:ba::/64 12 | 2001:4f8:3:ba:2e0:81ff:fe22:d1f1 13 | ::ffff:127.0.0.0/120 14 | ::ffff:127.0.0.0 (14 rows) openGauss=# DROP TABLE inet_test; DROP TABLE
macaddr
The macaddr 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 cases are accepted for the digits a through f. Output is always in the first of the forms shown.
Example:
openGauss=# CREATE TABLE macaddr_test(id int, m macaddr); CREATE TABLE openGauss=# INSERT INTO macaddr_test VALUES (1, '08:00:2b:01:02:03'); INSERT 0 1 openGauss=# INSERT INTO macaddr_test VALUES (2, '08-00-2b-01-02-03'); INSERT 0 1 openGauss=# INSERT INTO macaddr_test VALUES (3, '08002b:010203'); INSERT 0 1 openGauss=# INSERT INTO macaddr_test VALUES (4, '08002b-010203'); INSERT 0 1 openGauss=# INSERT INTO macaddr_test VALUES (5, '0800.2b01.0203'); INSERT 0 1 openGauss=# INSERT INTO macaddr_test VALUES (6, '08002b010203'); INSERT 0 1 openGauss=# SELECT * FROM macaddr_test ORDER BY id; id | m ----+------------------- 1 | 08:00:2b:01:02:03 2 | 08:00:2b:01:02:03 3 | 08:00:2b:01:02:03 4 | 08:00:2b:01:02:03 5 | 08:00:2b:01:02:03 6 | 08:00:2b:01:02:03 (6 rows) openGauss=# DROP TABLE macaddr_test; DROP TABLE
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot