Handling IP addresses using PostgreSQL

spitsman

Alexander Spitsyn

Posted on September 3, 2019

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
๐Ÿ’– ๐Ÿ’ช ๐Ÿ™… ๐Ÿšฉ
spitsman
Alexander Spitsyn

Posted on September 3, 2019

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

ยฉ TheLazy.dev

About