CIDR and INET in PostgreSQL: What They Are and How to Use Them

In PostgreSQL, inet is a data type used to store individual IP addresses, while cidr stores IP networks with a mask (range of addresses). Both help manage IP data efficiently and support network operations.

How It Works

Think of inet as a way to store a single house address, like "192.168.1.5". It holds one IP address, either IPv4 or IPv6. On the other hand, cidr stores a whole neighborhood or block of addresses, like "192.168.1.0/24", which means all addresses from 192.168.1.0 to 192.168.1.255.

This is useful because networks often work with groups of IPs, not just one. PostgreSQL understands these types and lets you do things like check if an IP is inside a network or find the network size. It stores these values efficiently and validates them to avoid mistakes.

Example

This example shows how to create a table with inet and cidr columns, insert values, and query them.

sql

CREATE TABLE network_devices (
  device_name TEXT,
  ip_address inet,
  network cidr
);

INSERT INTO network_devices VALUES
('Router', '192.168.1.1', '192.168.1.0/24'),
('Server', '10.0.0.5', '10.0.0.0/16');

-- Find devices in the 192.168.1.0/24 network
SELECT device_name, ip_address
FROM network_devices
WHERE ip_address <<= '192.168.1.0/24';

Output

 device_name | ip_address 
-------------+------------
 Router      | 192.168.1.1
(1 row)

When to Use

Use inet when you need to store and work with single IP addresses, such as tracking device IPs or user connections. Use cidr when you want to store IP ranges or networks, like defining subnets or firewall rules.

For example, a company might store each employee's computer IP with inet and store office network ranges with cidr to check if devices belong to a certain network.

Key Points

• inet stores single IP addresses (IPv4 or IPv6).
• cidr stores IP networks with masks (ranges).
• Both types support network operations like containment checks.
• They help keep IP data accurate and easy to query.

Key Takeaways

inet is for single IP addresses; cidr is for IP networks.

Use these types to efficiently store and query IP-related data in PostgreSQL.

They support both IPv4 and IPv6 formats.

You can perform network operations like checking if an IP belongs to a network.

Choosing the right type depends on whether you handle individual IPs or IP ranges.