Handling IP addresses using PostgreSQL
Alexander Spitsyn
Posted on September 3, 2019
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
๐ ๐ช ๐
๐ฉ
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
authorization How to Set Up Authorization in a Bookstore Management System with Go, HTMX, and Permit.io
November 29, 2024
javascript ๐ Black Friday Deal Ending Soon: Lifetime Access for Just $20 / โน1600!
November 29, 2024