How to Use Network Address Data Types in PostgreSQL

Storing IP addresses as plain text columns is one of those things that seems fine at first and then quietly causes problems for years — no real validation, no efficient range queries, no built-in understanding of subnets, and comparisons that don’t actually reflect network topology. PostgreSQL solves this properly with a dedicated set of network address types, and once you’ve used them, going back to varchar for IP data feels genuinely painful.

This article covers the three network address types PostgreSQL provides — cidr, inet, and macaddr (plus macaddr8) — with syntax, real examples, common use cases like access control and subnet analysis, and the troubleshooting knowledge you need to avoid the classic mistakes.

The Network Address Types

PostgreSQL provides four related types for network data:

• inet — stores an IPv4 or IPv6 host address, optionally with a subnet/netmask.
• cidr — stores an IPv4 or IPv6 network (subnet), stricter than inet about not allowing bits set outside the network mask.
• macaddr — stores a 6-byte MAC (hardware) address.
• macaddr8 — stores an 8-byte MAC address (EUI-64 format).

The distinction between inet and cidr trips a lot of people up initially, so let’s get that clear before anything else.

inet vs cidr: What’s the Real Difference?

• inet represents a specific host, optionally with a network prefix. It’s flexible — the address can have any host bits set alongside a netmask, since it’s meant to describe “this particular machine, which happens to live on this particular subnet.”
• cidr represents a network itself. Postgres enforces that no host bits are set beyond the netmask — if you try to insert a cidr value where the host portion isn’t all zeros relative to the prefix, it will error out.

SELECT '192.168.1.5/24'::inet;
-- 192.168.1.5/24   (valid — inet allows host bits set)

SELECT '192.168.1.5/24'::cidr;
-- ERROR:  invalid cidr value: "192.168.1.5/24"
-- (host bits set beyond the /24 network boundary)

SELECT '192.168.1.0/24'::cidr;
-- 192.168.1.0/24   (valid — this is a proper network address)

The practical rule of thumb: use inet for storing addresses belonging to actual hosts (user IPs, server IPs, client connections). Use cidr for storing network ranges themselves (firewall rules, allowed subnets, routing tables).

Basic Syntax and Table Setup

CREATE TABLE access_logs (
    log_id serial PRIMARY KEY,
    client_ip inet NOT NULL,
    accessed_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE allowed_networks (
    network_id serial PRIMARY KEY,
    network cidr NOT NULL,
    description text
);

CREATE TABLE network_devices (
    device_id serial PRIMARY KEY,
    device_name text,
    mac_address macaddr
);

Inserting values:

INSERT INTO access_logs (client_ip) VALUES ('203.0.113.42');
INSERT INTO access_logs (client_ip) VALUES ('2001:db8::1');  -- IPv6 works natively

INSERT INTO allowed_networks (network, description)
VALUES ('10.0.0.0/8', 'Internal corporate network');

INSERT INTO network_devices (device_name, mac_address)
VALUES ('Office Router', '08:00:2b:01:02:03');

Both IPv4 and IPv6 are supported transparently by inet and cidr — there’s no separate type for each, which is a genuine convenience compared to systems that force you to choose upfront.

Network Containment Operators

This is the single most useful feature of these types: PostgreSQL gives you dedicated operators for checking whether an address falls within a network, without any manual bitmasking arithmetic.

SELECT '192.168.1.10'::inet <<= '192.168.1.0/24'::cidr;
-- true  (is contained within or equal to)

SELECT '192.168.1.0/24'::cidr >>= '192.168.1.10'::inet;
-- true  (contains or equals)

SELECT '10.0.0.5'::inet << '10.0.0.0/8'::cidr;
-- true  (strictly contained within)

Here’s the full set of containment operators:

Operator	Meaning
<<	is strictly contained within
<<=	is contained within or equal to
>>	strictly contains
>>=	contains or equal to
&&	overlaps

A real-world query using this — checking whether a client’s IP falls within any allowed network:

SELECT a.client_ip, n.description
FROM access_logs a
JOIN allowed_networks n ON a.client_ip <<= n.network;

This single line replaces what would otherwise be painful manual subnet math in application code.

Extracting Parts of an Address

SELECT host('192.168.1.10/24'::inet);       -- 192.168.1.10
SELECT netmask('192.168.1.10/24'::inet);    -- 255.255.255.0
SELECT broadcast('192.168.1.10/24'::inet);  -- 192.168.1.255
SELECT network('192.168.1.10/24'::inet);    -- 192.168.1.0/24
SELECT masklen('192.168.1.10/24'::inet);    -- 24
SELECT family('192.168.1.10'::inet);        -- 4  (IPv4)
SELECT family('2001:db8::1'::inet);         -- 6  (IPv6)

host() is particularly useful when you want to strip the netmask and just get the bare address as text for display or logging purposes.

Comparison and Sorting

inet and cidr values support ordering, and they sort in a genuinely meaningful way — by address family first (IPv4 before IPv6), then numerically by the address itself:

SELECT client_ip
FROM access_logs
ORDER BY client_ip;

This is a real advantage over text-based storage, where sorting '10.0.0.9' and '10.0.0.10' alphabetically gives you the wrong order entirely ('10.0.0.10' sorts before '10.0.0.9' as text). With inet, you get correct numeric address ordering automatically.

Indexing Network Columns

Standard B-tree indexes work fine for equality and ordering:

CREATE INDEX idx_access_logs_ip ON access_logs (client_ip);

But for containment queries (<<, <<=, >>, >>=, &&), a plain B-tree index doesn’t help efficiently at scale. For that, PostgreSQL supports GiST indexes on inet/cidr columns via the btree_gist extension:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE INDEX idx_allowed_networks_gist ON allowed_networks USING gist (network inet_ops);

This makes containment-based lookups (like the “is this IP inside any allowed network” query above) genuinely fast even against large tables of network ranges, instead of forcing a sequential scan.

Working with MAC Addresses

macaddr handles standard 6-byte hardware addresses, and normalizes formatting automatically:

SELECT '08:00:2b:01:02:03'::macaddr;
-- 08:00:2b:01:02:03

SELECT '08-00-2b-01-02-03'::macaddr;
-- 08:00:2b:01:02:03  (different input format, same normalized output)

SELECT '0800.2b01.0203'::macaddr;
-- 08:00:2b:01:02:03  (Cisco-style notation also accepted)

This format flexibility on input, combined with consistent normalized output, is genuinely helpful when you’re ingesting MAC addresses from different vendors and tools that each format them slightly differently.

macaddr8 handles the newer 8-byte EUI-64 format, increasingly common with IPv6-related hardware addressing:

SELECT '08:00:2b:01:02:03:04:05'::macaddr8;

You can also convert a standard 6-byte macaddr into macaddr8 format:

SELECT macaddr8_set7bit('08:00:2b:01:02:03'::macaddr8);

Practical Use Cases

1. Access Control and Firewall-Style Rules

CREATE TABLE ip_allowlist (
    id serial PRIMARY KEY,
    network cidr NOT NULL,
    allowed boolean NOT NULL DEFAULT true
);

INSERT INTO ip_allowlist (network) VALUES ('192.168.0.0/16'), ('10.0.0.0/8');

-- Check access for an incoming request
SELECT EXISTS (
    SELECT 1 FROM ip_allowlist
    WHERE allowed = true AND '192.168.5.20'::inet <<= network
) AS is_allowed;

2. Login and Request Logging with Geo/Network Analysis

SELECT network(client_ip::cidr) AS subnet, count(*) AS requests
FROM access_logs
WHERE family(client_ip) = 4
GROUP BY network(client_ip::cidr)
ORDER BY requests DESC
LIMIT 10;

This kind of query — grouping raw request logs by subnet to spot patterns like distributed abuse from a single network range — is exactly the kind of thing that’s painful with text columns and trivial with inet.

3. Device Inventory Tracking

Storing MAC addresses properly (rather than as free-text strings) means you get automatic normalization and can reliably deduplicate or join against known hardware regardless of the formatting convention used by whatever tool captured the data originally.

4. Detecting Overlapping Network Ranges

SELECT a.network, b.network
FROM allowed_networks a
JOIN allowed_networks b ON a.network && b.network AND a.network_id < b.network_id;

This finds any pairs of configured network ranges that overlap — useful for catching misconfigured firewall or routing rules before they cause problems.

Troubleshooting Common Issues

“Invalid cidr value” errors. This means you’re trying to insert an address with host bits set into a cidr column. Either use inet instead, or zero out the host bits — use network(your_address::inet) to get the proper network address before casting to cidr.

Confusing which operator points which direction. The mnemonic that helps most people: the “open” side of << or >> points toward the bigger/containing network. << (opening toward the right) means “contained within,” >> means “contains.”

Losing subnet information accidentally. Casting inet to text and back can lose the netmask if you’re not careful about formatting. Always store the full CIDR notation (address/prefix) rather than assuming a default prefix length.

IPv6 addresses not matching expected containment. Remember that IPv4 and IPv6 addresses are entirely separate address families — an IPv4-mapped IPv6 address won’t automatically match a plain IPv4 cidr range unless you handle that mapping explicitly.

Slow containment queries at scale. As covered above, this almost always means you’re missing a GiST index (via btree_gist) on the network column and are falling back to sequential scans.

Best Practices

• Use inet for host addresses (user connections, server IPs); use cidr for network/subnet definitions (firewall rules, allowlists).
• Always create a GiST index via btree_gist if you’re doing containment queries against a non-trivial table size.
• Normalize addresses using network() before storing them as cidr to avoid insertion errors from stray host bits.
• Store MAC addresses as macaddr/macaddr8 rather than text — you get free normalization and consistent formatting regardless of source format.
• Take advantage of family() when your data mixes IPv4 and IPv6, since a lot of logic (subnet math, containment) behaves differently between the two.
• Don’t reach for regex-based text validation on IP columns — it’s slower, less accurate, and reinvents something Postgres already does correctly and efficiently.

Combining Network Types with Other Columns

In practice, network address data rarely lives in isolation — it’s usually part of a broader logging, security, or asset-tracking table. Here’s a more complete example that ties several of the concepts above together into something closer to a real production schema.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE firewall_rules (
    rule_id serial PRIMARY KEY,
    rule_name text NOT NULL,
    source_network cidr NOT NULL,
    action text NOT NULL CHECK (action IN ('allow', 'deny')),
    priority integer NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_firewall_rules_gist ON firewall_rules USING gist (source_network inet_ops);

CREATE OR REPLACE FUNCTION evaluate_ip(p_ip inet)
RETURNS text AS $$
DECLARE
    matched_action text;
BEGIN
    SELECT action INTO matched_action
    FROM firewall_rules
    WHERE p_ip <<= source_network
    ORDER BY priority ASC
    LIMIT 1;

    RETURN COALESCE(matched_action, 'deny');
END;
$$ LANGUAGE plpgsql;

SELECT evaluate_ip('192.168.1.50');

This function evaluates an incoming IP against the highest-priority matching rule, falling back to a default deny if nothing matches — a genuinely realistic pattern for anything from an application-level access gate to a lightweight rule engine sitting in front of more expensive downstream logic.

Aggregating and Reporting on Network Data

Beyond simple containment checks, network types support useful aggregate-style reporting queries that would be considerably more painful with text-based IP storage:

-- Count distinct /24 subnets seen in access logs over the last day
SELECT count(DISTINCT set_masklen(client_ip, 24)) AS distinct_subnets
FROM access_logs
WHERE accessed_at > now() - interval '1 day'
  AND family(client_ip) = 4;

set_masklen() lets you re-mask an address to a different prefix length on the fly, which is exactly what you need for “bucket these addresses by subnet” style reporting without needing to pre-store a separate subnet column.

SELECT set_masklen('192.168.1.55'::inet, 24);
-- 192.168.1.55/24 (netmask changed, address unchanged)

Note the distinction from network(), which zeroes out the host bits — set_masklen() just changes the prefix length while keeping the host portion intact, which is exactly the behavior you want for grouping/bucketing queries like the one above.

Security and Auditing Use Cases

Because network types support real comparison and containment logic, they’re a natural fit for basic anomaly detection queries that would otherwise require pulling data out of the database and processing it in application code:

-- Flag IPs making requests from outside all known allowed ranges
SELECT DISTINCT a.client_ip
FROM access_logs a
WHERE NOT EXISTS (
    SELECT 1 FROM allowed_networks n WHERE a.client_ip <<= n.network
)
AND a.accessed_at > now() - interval '1 hour';

-- Detect a single host generating requests classified under multiple different subnets
-- (potentially indicative of IP spoofing in certain contexts)
SELECT client_ip, count(DISTINCT set_masklen(client_ip, 16)) AS subnet_variety
FROM access_logs
GROUP BY client_ip
HAVING count(DISTINCT set_masklen(client_ip, 16)) > 1;

These are the kinds of queries that are trivial with proper inet/cidr columns and genuinely painful — often requiring external scripting — against plain text IP storage.

Working with IPv6 Specifically

Everything covered so far applies equally to IPv4 and IPv6, but IPv6 brings a couple of quirks worth calling out explicitly since it comes up less often in day-to-day work.

SELECT '2001:db8::/32'::cidr;
SELECT '::1'::inet;              -- IPv6 loopback
SELECT '2001:db8::1'::inet <<= '2001:db8::/32'::cidr;  -- true

Because IPv6 addresses are 128 bits versus IPv4’s 32, prefix lengths behave very differently in practice — a /32 in IPv6 covers an enormous address space (often allocated to an entire organization), whereas a /32 in IPv4 is a single host. Don’t assume prefix-length intuition built from IPv4 work carries over directly to IPv6 without adjustment.

Mixed IPv4/IPv6 tables are common in access logs, since modern clients may connect over either protocol:

SELECT family(client_ip), count(*)
FROM access_logs
GROUP BY family(client_ip);

This kind of query is a quick, useful sanity check when you’re debugging why a containment query against an IPv4 cidr range isn’t matching some rows — they may simply be IPv6 connections that were never going to match an IPv4-only range in the first place.

Storing Network Data Alongside Geolocation

A common real-world extension of network address data is pairing it with geolocation lookups, typically from an external IP-to-location dataset loaded into its own table:

CREATE TABLE ip_geolocation (
    network cidr PRIMARY KEY,
    country_code char(2),
    region text
);

CREATE INDEX idx_ip_geo_gist ON ip_geolocation USING gist (network inet_ops);

SELECT g.country_code, g.region
FROM access_logs a
JOIN ip_geolocation g ON a.client_ip <<= g.network
WHERE a.log_id = 12345;

This join-on-containment pattern — matching a specific host address against the broadest applicable network range in a reference table — is exactly the kind of query that would be painful to express correctly with text-based IP columns, and it’s a very natural fit once both sides of the join are using proper inet/cidr types with a GiST index in place.

Quick Reference for Common Network Functions

A short cheat sheet worth keeping nearby when working with inet/cidr regularly:

SELECT abbrev('192.168.1.0/24'::cidr);   -- compact text form
SELECT inet_same_family('10.0.0.1'::inet, '2001:db8::1'::inet);  -- false, mixed families
SELECT host('10.0.0.5/24'::inet);         -- 10.0.0.5, address without the mask

inet_same_family() is a small but genuinely handy guard to add before running comparisons that assume both sides are the same address family — comparing an IPv4 and IPv6 address directly with containment operators will simply never match, and this function makes that assumption explicit and testable in your own logic rather than silently returning false with no clear explanation.

Handling Loopback and Private Address Ranges

Application logic often needs to distinguish traffic from private, internal networks versus genuinely external, public addresses — a common need in logging dashboards where you want to filter out internal health checks and monitoring traffic:

SELECT client_ip, accessed_at
FROM access_logs
WHERE NOT (
    client_ip <<= '10.0.0.0/8'::cidr OR
    client_ip <<= '172.16.0.0/12'::cidr OR
    client_ip <<= '192.168.0.0/16'::cidr OR
    client_ip <<= '127.0.0.0/8'::cidr
);

Wrapping this repeated logic in a small reusable function keeps it consistent everywhere it’s needed:

CREATE OR REPLACE FUNCTION is_private_address(p_ip inet)
RETURNS boolean AS $$
BEGIN
    RETURN p_ip <<= '10.0.0.0/8'::cidr
        OR p_ip <<= '172.16.0.0/12'::cidr
        OR p_ip <<= '192.168.0.0/16'::cidr
        OR p_ip <<= '127.0.0.0/8'::cidr;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Marking it IMMUTABLE lets Postgres safely use it in index expressions or cache its results within a single query plan where applicable, since the private-address ranges being checked against are fixed constants.

Wrapping Up

Network address types are one of PostgreSQL’s most underused features relative to how genuinely useful they are. If your schema has any column storing an IP address or MAC address as plain text, converting it to inet, cidr, or macaddr gets you real validation, correct sorting, efficient containment queries, and a set of purpose-built operators that would otherwise require reinventing subnet math by hand. For anything touching access control, network logging, or device inventory, these types aren’t just a nice-to-have — they’re the correct tool for the job.