Today I Learned

This project exists to catalogue the sharing & accumulation of knowledge as it happens day-to-day. Posts have a 200-word limit, and posting is open to any Rocketeer as well as selected friends of JetRockets. We hope you enjoy learning along with us.

1 post about #postgres

Handling IP addresses using PostgreSQL

PostgreSQL provides a inet and cidr datatypes for storing net addresses and proceed operations with them.

Host address and it’s subnet can be stored with inet, while cidr can contain only network address:

select inet '';
select cidr ''; -- valid cidr
select cidr ''; -- invalid: cidr must not be a host address
ERROR:  invalid cidr value: ""
LINE 1: select cidr '';
DETAIL:  Value has bits set to right of mask.

In case there’s no number after slash in cidr address the netmask is to equal 32:

select cidr('');

The value above represents a subnet address, while the same value passed to inet represents a host:

select inet('');

Checking inclusion or equality can be performed with >>= and <<= operators:

select inet '' >>= inet ''; -- returns true
select cidr '' >>= inet ''; -- returns false
select cidr '' >>= cidr ''; -- returns true

And getting a netmask by a net address can be performed with netmask:

select netmask(inet('')); -- returns
select netmask(cidr('')); -- returns