How to Store an IP Address in PostgreSQL: inet and cidr

PostgreSQL is the rare database that has a real type for this, so you almost never store an IP address as text. Use inet for a single host address: it holds IPv4 or IPv6 in one column, optionally carries a netmask, validates the address on insert, and comes with operators that answer "is this IP inside that subnet" directly. Use cidr when the column holds a network or subnet rather than a host, because cidr additionally rejects any address with bits set to the right of the netmask. Reach for text/varchar only when you have a specific reason to keep the raw string and will never compare, sort, or subnet-match. Below is the comparison table, a worked access_log schema, the containment operators, and how this contrasts with the manual approach MySQL forces on you.

Short answer: store a host address in inet, a subnet in cidr. Both are 7 bytes for IPv4 and 19 bytes for IPv6, both validate input and sort in true address order, and both support the containment operators (<<, <<=, >>, >>=, &&) so a subnet match is WHERE ip <<= '10.0.0.0/8'::cidr with no integer-conversion math. Do not default to text: it loses validation, the operators, and correct ordering.

Why PostgreSQL is different here

In MySQL there is no IP type, so storing an IP address in MySQL means packing the bytes yourself: INT UNSIGNED plus INET_ATON() for IPv4-only, or VARBINARY(16) plus INET6_ATON() to cover IPv6. You manage the conversion on every insert and read, and a subnet test becomes a BETWEEN against the network and broadcast addresses you compute by hand.

PostgreSQL hands you all of that for free. The inet type is a first-class network type: it parses and validates the address, stores IPv4 and IPv6 in the same column without you choosing a width, keeps an optional netmask attached to the value, and ships specialized operators and functions built for exactly these comparisons. You write the dotted string, Postgres stores the structured value, and "is this address in this subnet" is a single operator. This is one of the cleaner wins Postgres has over MySQL for application schemas, and it is worth using instead of recreating the MySQL pattern out of habit.

inet vs cidr vs text

The two native types differ in one rule. inet accepts an address with nonzero bits to the right of the netmask, so 192.168.0.1/24 is a valid inet (host .1 inside the /24). cidr rejects that same value, because a cidr is a network specification and must have a clean netmask boundary: 192.168.0.0/24 is a valid cidr, 192.168.0.1/24 is an error. So inet is for "a host, which happens to live in some subnet" and cidr is for "a subnet" itself.

The storage figures come straight from the type documentation: both inet and cidr occupy 7 bytes for an IPv4 value and 19 bytes for an IPv6 value. A dotted-decimal string is not dramatically larger for IPv4, but the string loses validation, loses correct ordering (10.0.0.2 sorts after 10.0.0.10 lexically), and has no expressible subnet query at all. The native type is the right default and the string is the exception.

A worked schema: access_log

A realistic access log stores the client address in inet, because each row is a single host, not a network. The address gets indexed for per-IP lookups.

CREATE TABLE access_log (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ip          inet NOT NULL,
  path        text NOT NULL,
  status      smallint NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_access_log_ip_created ON access_log (ip, created_at);

-- log a request: pass the client IP as a plain string, Postgres validates and stores it
INSERT INTO access_log (ip, path, status)
VALUES ('203.0.113.7', '/login', 200),
       ('2001:db8::1', '/login', 200);

-- read it back: it comes out as a normalized address, no conversion function needed
SELECT ip, path, status, created_at
FROM access_log
WHERE ip = '203.0.113.7'
ORDER BY created_at DESC;

No INET_ATON(), no HEX(), no application-side packing. You insert the string, Postgres parses and validates it (a malformed address raises an error on insert instead of landing as silent garbage), and it reads back as a clean address value. The composite (ip, created_at) index serves the equality on ip and the ordering on created_at from one B-tree, which is the query an access log exists to answer.

Subnet matching with the containment operators

This is where the native type earns its place. The containment operators consider the network parts of the two addresses and test whether one is contained in, contains, or overlaps the other:

• a << b true when a is strictly contained within network b.
• a <<= b true when a is contained within b or equal to it.
• a >> b true when a strictly contains b.
• a >>= b true when a contains b or equals it.
• a && b true when either network contains or equals the other (they overlap).

So "every logged hit from the 10.0.0.0/8 private range" is one expression, no boundary math:

SELECT ip, path, created_at
FROM access_log
WHERE ip <<= '10.0.0.0/8'::cidr
ORDER BY created_at DESC;

Compare that to the MySQL version, where the same question is a BETWEEN against the integer values of the first and last address in the block that you have to compute first. Here the subnet is written literally and the operator does the containment.

A handful of functions round out the toolkit when you need to pick a value apart:

• host(ip) returns just the address as text, dropping the netmask.
• masklen(ip) returns the netmask length in bits.
• network(ip) returns the network part as a cidr (equivalent to casting to cidr).
• abbrev(ip) gives the short display form.
• family(ip) returns 4 for IPv4 and 6 for IPv6, which is the clean way to filter or branch on address family in one mixed column.

-- split IPv4 and IPv6 traffic without parsing strings
SELECT family(ip) AS ver, count(*)
FROM access_log
GROUP BY family(ip);

Indexing for subnet queries: GiST and inet_ops

A plain B-tree index covers equality and ordering on an inet column, which is what the access_log example above uses. But the containment operators (<<, <<=, >>, >>=, &&) are not B-tree operators, so a subnet-match query will not use that index for the containment test. For those, PostgreSQL ships a GiST operator class called inet_ops.

The catch worth knowing: for historical reasons inet_ops is not the default operator class for inet and cidr, so you have to name it explicitly in CREATE INDEX or you get a default GiST index that does not help:

CREATE INDEX idx_access_log_ip_gist
  ON access_log USING gist (ip inet_ops);

With that index in place, WHERE ip <<= '10.0.0.0/8'::cidr can use the GiST structure instead of scanning the table. If your workload is mostly exact-address lookups, keep the B-tree; if it is mostly "which rows fall inside this block", add the inet_ops GiST index. Many access-log tables end up with both.

When text is actually the right call

There is a narrow case for storing the raw string: you are capturing an address exactly as a client sent it, including malformed or attacker-supplied values, and you specifically do not want Postgres to reject or normalize it on insert. A WAF log or a raw request-capture table might want the literal bytes for forensics. That is a real reason, and text is fine there. For everything else, where the address is a real address you will compare, sort, or subnet-match, inet is the default and the string is the mistake.

What to do next

• The MySQL counterpart, where there is no native type and you pack the bytes yourself, is How to Store an IP Address in MySQL. The contrast is the whole point: Postgres gives you inet/cidr, MySQL makes you build it.
• For storing fixed-width digests rather than addresses, see How to Store a SHA-256 Hash in MySQL; the same "store the typed value, not the string" instinct applies.

FAQ

See also

• Storing an IP address in MySQL, the no-native-type counterpart where you pack the bytes with INET6_ATON() yourself.
• Storing arrays in PostgreSQL for another case where a Postgres-native type beats serializing into a string.
• Choosing between JSON and JSONB in PostgreSQL when the column holds structured data rather than a scalar.
• Storing a UUID in PostgreSQL for the typed-key version of the same instinct: store the value, not its text form.
• Running PostgreSQL in Docker to spin up a throwaway instance and try the inet/cidr schema above.