PostgreSQL Network Address Functions

`host()` always strips the prefix and returns a bare IP string, but for user-facing display `abbrev()` is more informative — it drops the /32 suffix for single hosts while keeping the prefix for networks. Defaulting to `host()` everywhere discards CIDR context that security reviewers often need.

✓ Instead: Use `abbrev(ip_col)` for display: it renders single-host /32 addresses as plain IPs and keeps meaningful prefixes on network addresses, so the output is compact without throwing away subnet information.

Some schemas store an IP address as `TEXT` and prefix length as a separate `INTEGER` column, then use `masklen()` to reconstruct logic at query time. This splits what is naturally a single value, requires extra joins or concatenation to rebuild a usable CIDR, and prevents the optimizer from using GiST indexes on containment queries.

✓ Instead: Store the address and its prefix together as `inet` or `cidr`. Use `masklen(col)` only to read or filter the prefix — not as a substitute for proper type design.

A common mistake is extracting the network address with `network(ip_col)` and then doing string or equality comparisons to check subnet membership, e.g., `WHERE network(ip_address) = '10.0.0.0/8'`. This only matches addresses that are exactly in that network at exactly that prefix length, missing all longer-prefix subnets contained within it.

✓ Instead: Use the containment operator directly: `WHERE ip_address << '10.0.0.0/8'::cidr` (strictly contained) or `WHERE ip_address <<= '10.0.0.0/8'::cidr` (contained or equal). These operators work with GiST indexes and correctly handle nested subnets.

Some developers store IPs as text and compute broadcast addresses with string manipulation or by casting to integers and using bitwise OR. This is error-prone for IPv6, breaks on unexpected prefix lengths, and cannot leverage PostgreSQL's native operator optimizations.

✓ Instead: Store addresses as `inet` or `cidr` and call `broadcast(col)` directly. For range queries, prefer the `<<` and `<<=` containment operators over computing and comparing broadcast boundaries manually.

Applying the `<<` (contained-within) or `=` operators between an IPv4 address and an IPv6 CIDR silently returns false rather than raising an error. In access-control logic this can cause allowlist rules to be silently skipped, creating security gaps that are hard to detect in testing if your test data happens to be single-family.

✓ Instead: Always guard cross-family comparisons: `WHERE inet_same_family(client_ip, allowed_cidr) AND client_ip <<= allowed_cidr`. In dual-stack environments, store separate allowlist rows for IPv4 and IPv6, or use `family(col)` in a partial index to keep them segregated.

Some schemas add an explicit `ip_version` column (e.g., `'IPv4'` or `4`) alongside the `inet` column. This duplicates information already encoded in the `inet` type, introduces the risk of the columns drifting out of sync, and adds unnecessary storage and maintenance overhead.

✓ Instead: Derive the IP version on demand with `family(ip_col)`. If you need to index by family, use a partial index: `CREATE INDEX ON connections (client_ip) WHERE family(client_ip) = 6;` rather than a redundant column.

`host()` always strips the prefix entirely, producing a bare IP string. For network addresses like `10.0.0.0/8` this throws away the prefix, leaving just `10.0.0.0` — which loses the subnet context. `abbrev()` keeps the prefix for non-default lengths and only drops it when it is trivial (/32, /128).

✓ Instead: Prefer `abbrev(col)` for display output. Reserve `host(col)` for cases where you specifically need a bare IP string with no prefix information, such as DNS lookups or legacy text comparisons.

Storing MAC addresses as `TEXT` and then extracting the OUI with `SUBSTRING(mac, 1, 8)` or `SPLIT_PART(mac, ':', 1) || ':' || ...` is fragile: it breaks on different separator styles (colons vs dashes vs no separator), mixed case, and short-form notation. It also prevents using `macaddr` operators for sorting and comparison.

✓ Instead: Store MAC addresses as the `macaddr` type and use `trunc(mac_col)` to extract the OUI. PostgreSQL normalises the format on input, so all comparison and grouping operations are reliable regardless of how the MAC was originally entered.

Using `inet_merge` and the `<<` / `<<=` operators for subnet lookups without a GiST index causes sequential scans. On tables with millions of IP rows — typical in firewall logs, connection tables, or access-control lists — this can make subnet queries orders of magnitude slower than necessary.

✓ Instead: Create a GiST index on any `inet` or `cidr` column that participates in containment queries: `CREATE INDEX ON firewall_rules USING GIST (allowed_cidr inet_ops);`. The `inet_ops` operator class enables fast `<<`, `<<=`, `>>`, and `>>=` lookups without a full scan.