Introduction to PostgreSQL cidr Data Type

cidr is a data type in PostgreSQL used to store IPv4 or IPv6 addresses and subnet masks.

Syntax

The cidr data type can be declared using the following syntax:

CIDR [ (n) ]

where n is an optional integer that represents the number of mask bits.

Use Cases

The cidr data type is typically used to store IPv4 or IPv6 addresses and subnet masks. Using the cidr data type makes it convenient to compare and perform calculations on IP addresses.

Examples

Here are two examples of using the cidr data type.

Example 1

Suppose we have a table named ip_table with two columns: id and ip_addr. The ip_addr column stores IP addresses and subnet masks, and we can use the cidr data type to store them.

CREATE TABLE ip_table (
    id SERIAL PRIMARY KEY,
    ip_addr CIDR
);

INSERT INTO ip_table (ip_addr) VALUES ('192.168.1.0/24');
INSERT INTO ip_table (ip_addr) VALUES ('2001:db8::/32');

Next, we can use the following query to find the subnet where a specific IP address belongs:

SELECT id, ip_addr
FROM ip_table
WHERE '192.168.1.1' << ip_addr;

The above query will return the subnet in ip_table that contains ‘192.168.1.1’.

Example 2

In this example, we will use the cidr data type to calculate the distance between two IP addresses.

SELECT '192.168.0.0/24'::cidr - '192.168.0.10/32'::cidr AS distance;

The above query will return 24, which represents the distance in bits between the first and second IP addresses.

Conclusion

The cidr data type is a convenient data type in PostgreSQL for storing IPv4 or IPv6 addresses and subnet masks, and it allows for easy comparison and calculation of IP addresses.