In PostgreSQL, why store visitor IP addresses as inet rather than text, and when would you use cidr instead?
answer
- validation on input
- numeric order, not string order
- subnet containment operators
- the netmask is part of the value
- networks only, no host bits
basics
~20 sinet validates IPv4 and IPv6 input, stores an address with an optional netmask, sorts numerically and supports subnet operators such as <<=. cidr holds only networks and rejects input with bits set right of the mask.
solid answer
~40 sText accepts anything and compares character by character, so `'10.0.0.9'` sorts after `'10.0.0.10'` and "is this address in that subnet" becomes string hacks. `inet` rejects malformed input, stores IPv4 or IPv6 in 7 or 19 bytes, orders numerically with IPv4 before IPv6, and supports `client_ip <<= '203.0.113.0/24'` plus `network()`, `host()`, `masklen()` and `family()`. The netmask is part of the value, so `inet '192.0.2.5'` and `inet '192.0.2.5/24'` are not equal, and casting to text yields `192.0.2.5/32`; use `host()` for the bare address. I use `cidr` for network entries such as a blocklist: it accepts only a network, so `'192.0.2.5/24'::cidr` fails with `Value has bits set to right of mask`, while an explicit `inet`-to-`cidr` cast silently zeroes those bits.
code
sql · 22 linesCREATE TABLE login_attempt (
attempted_at timestamptz NOT NULL DEFAULT now(),
client_ip inet NOT NULL
);
CREATE TABLE blocked_network (
net cidr PRIMARY KEY
);
INSERT INTO blocked_network VALUES ('203.0.113.0/24');
-- INSERT INTO blocked_network VALUES ('203.0.113.7/24');
-- ERROR: invalid cidr value: "203.0.113.7/24"
SELECT host(a.client_ip), a.attempted_at
FROM login_attempt AS a
WHERE EXISTS (SELECT 1 FROM blocked_network AS b
WHERE a.client_ip <<= b.net);
SELECT network(set_masklen(client_ip, 24)) AS subnet, count(*)
FROM login_attempt
WHERE family(client_ip) = 4
GROUP BY 1
ORDER BY 2 DESC;go deeper
Know that inet stores IPv4 and IPv6 addresses with validation, and that cidr is for networks.
Explain subnet operators like <<=, the functions host() and network(), and why the netmask makes two similar inet values unequal.
Spot the traps in real code: text casts adding /32, equality misses on masked values, and silent host-bit zeroing on inet-to-cidr casts.
Decide how network data is modelled across services: observed hosts as inet, configured networks as cidr, and one convention for masks.
## Why not text A recipe site logs the client address of every login attempt so it can rate-limit and block abusive networks. Stored as `text`, the column has three problems: - **No validation.** `'999.1.1.1'`, `'localhost'` and trailing spaces are all accepted. - **Wrong ordering.** Text compares character by character, so `'10.0.0.9'` sorts after `'10.0.0.10'`, and IPv6 spellings of one address differ. - **No network logic.** "Is this address in 203.0.113.0/24" turns into `LIKE '203.0.113.%'`, which works only on octet boundaries and never for IPv6. PostgreSQL's network types solve all three: the manual recommends them over text because they validate input and come with specialised operators and functions. ## inet: a host address with an optional netmask `inet` holds an IPv4 or IPv6 address and, optionally, the subnet it belongs to, in 7 bytes for IPv4 or 19 bytes for IPv6. Input is `address/y`, where `y` is the number of netmask bits; without `/y` the mask is 32 for IPv4 or 128 for IPv6, meaning a single host, and the mask is then omitted on display. The netmask is **part of the value**. Comparison looks at the network bits, then the mask length, then the full address, so `inet '192.0.2.5' = inet '192.0.2.5/24'` is false. When sorting, all IPv4 addresses come before IPv6 ones, including IPv4-mapped forms such as `::ffff:10.4.3.2`. ## cidr: networks only `cidr` holds a **network** specification and nothing else. Its input names the network's lowest address, and it is an error to have bits set to the right of the mask: `'192.0.2.5/24'::cidr` fails with `invalid cidr value` and the detail `Value has bits set to right of mask`. If `/y` is omitted, the mask is inferred from the old classful rules, widened to cover every octet written, so `cidr '192.168.1'` is `192.168.1.0/24`. Two casts behave differently: 1. `cidr` to `inet` is implicit, so every `inet` operator and function also accepts `cidr`. 2. `inet` to `cidr` is allowed explicitly and **silently zeroes** the host bits: `inet '192.0.2.5/24'::cidr` becomes `192.0.2.0/24`. Use `inet` for addresses you observe and `cidr` for networks you configure, such as a table of blocked ranges. ## Operators and functions you will actually use | Expression | Result | Use | |---|---|---| | `ip <<= '203.0.113.0/24'` | true if inside or equal | is the visitor in a blocked network | | `net >> ip` | true if strictly contains | the strict form, written from the network side | | `a && b` | true if either contains the other | do two networks intersect | | `network('203.0.113.7/24')` | `203.0.113.0/24` | group visitors by subnet | | `set_masklen(ip, 24)` | same address, new mask | combine with `network()` to bucket hosts | | `host(ip)` | `'203.0.113.7'` | the bare address as text | | `masklen(ip)`, `family(ip)` | `24`, `4` or `6` | inspect the value | | `inet_merge(a, b)` | smallest network covering both | summarise ranges | ## Traps - **Text casts keep the mask.** `inet '203.0.113.7'::text` is `'203.0.113.7/32'`, not the bare address. Use `host()` for display or for comparison with text from an application. - **Equality includes the mask.** A lookup with `client_ip = '203.0.113.7'` misses rows stored as `203.0.113.7/24`. Store hosts without a mask, or compare with `host()`. - **`LIKE` does not apply.** `inet` has no pattern operator; containment operators are the replacement. - **Indexes need a deliberate choice.** Subnet operators want a GiST index, and the built-in `inet_ops` GiST class is not the default for `inet`, so it must be named; index details belong with GiST indexing. - **Families sort apart.** All IPv4 values sort before all IPv6 values, and an IPv4-mapped address such as `::ffff:203.0.113.7` is IPv6 (`family()` returns 6), so a client seen through an IPv6 socket lands in the IPv6 part of a report and escapes a `family(client_ip) = 4` filter. ## Rate-limiting by /24 To count attempts per IPv4 /24, group by `network(set_masklen(client_ip, 24))`, which turns `203.0.113.7` into `203.0.113.0/24`. To refuse logins from blocked networks, store them in a `cidr` column and test `client_ip <<= ANY (SELECT net FROM blocked_network)`, or use `EXISTS` with the same predicate.
- Why does WHERE client_ip = '203.0.113.7' miss a row stored as 203.0.113.7/24?For `inet`, the netmask is part of the value: comparison checks the network bits, then the mask length, then the address. `203.0.113.7/32` and `203.0.113.7/24` differ in mask length, so they are not equal. Store observed hosts without a mask, or compare `host(client_ip)` with the text address.
- What happens when you cast inet '203.0.113.7/24' to cidr?The explicit cast is allowed and silently zeroes the bits right of the mask, giving `203.0.113.0/24`. That differs from typing the same text as a `cidr` literal, which fails with `invalid cidr value` because host bits are set. Use the cast deliberately when deriving a network, never to clean up input you meant to validate.
- Storing IP addresses as text is fine because ordering and comparison work the same.
- inet '192.0.2.5' equals inet '192.0.2.5/24' because the host part matches.
- Casting an inet to text returns the bare address without a mask.
- cidr accepts '203.0.113.7/24' and zeroes the host bits on input.
- client_ip LIKE '203.0.113.%' is the normal way to match a subnet.