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 '192.168.0.1/24';

      inet

----------------

 192.168.0.1/24

select cidr '192.168.0.0/24'; -- valid cidr

      cidr

----------------

 192.168.0.0/24

select cidr '192.168.0.1/24'; -- invalid: cidr must not be a host address

ERROR:  invalid cidr value: "192.168.0.1/24"

LINE 1: select cidr '192.168.0.1/24';

                    ^

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('127.0.0.1');

     cidr

--------------

 127.0.0.1/32

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

select inet('127.0.0.1');

   inet

-----------

 127.0.0.1

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

select inet '192.168.0.1/24' >>= inet '192.168.0.0'; -- returns true

select cidr '192.168.0.0/24' >>= inet '192.168.0.0/12'; -- returns false

select cidr '192.168.0.0' >>= cidr '192.168.0.0'; -- returns true

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

select netmask(inet('192.168.0.0/24')); -- returns 255.255.255.0

select netmask(cidr('127.0.0.1')); -- returns 255.255.255.255