PostgreSQL has a built-in type for IP addresses, and it’s called inet.
The inet type is used to store IP addresses in PostgreSQL and can handle both IPv4 and IPv6 addresses.
The official documentation provides a comprehensive guide in the Network Address Types section, detailing how to work with network addresses in PostgreSQL, including how to convert between different formats, perform operations on IP addresses, and more.
You might be tempted to use a varchar field to store network addresses, but this is not recommended. While varchar allows storing IP addresses as plain text, it lacks features such as IP address validation, efficient storage, and the ability to perform network-specific operations like subnet comparisons, which inet provides. If you need to store and, more importantly, perform operations on network addresses, it’s better to use the inet type. It is incredibly powerful.
Here are some examples of how to use the inet type:
-- Create a table with an IP address column
CREATE TABLE ip_addresses (
ip_address inet
);
The inet type can store both individual IP addresses and subnets (in CIDR notation). What’s really interesting is that when interacting with the database, PostgreSQL provides several network address functions and operators for working with the inet type, allowing you to perform operations such as comparisons, subnet matching, and more. The most evident example is searching for an IP address in a table where an inet field is stored, regardless of whether it’s an IPv4 or IPv6 address, with or without a subnet.
Here’s an example of how to find an IP address match in a table, comparing with both IP and subnet, and finding rows with network addresses that contain a specific IP address. The >> operator checks if the network on the left side contains the IP address on the right side. For example, the query below finds all rows where the subnet contains the IP ‘192.168.1.1’.
SELECT * FROM ip_addresses WHERE ip_address >> inet '192.168.1.1';
The full list of operations you can perform with the inet feature can be found in the official documentation.
As an additional note, be aware that some ORM systems like Prisma do not fully support these data types and operators, meaning you may need to write SQL queries manually to interact with the database. You can find a related issue report in the Prisma repository: Prisma issue #24129.