CODEWITHSUNDEEP
sqlpostgresql
Foad Tahmasebi
Foad Tahmasebi

Reputation: 1342

how to select IPs in same network in SQL

I have a table include IP addresses and their netmasks, now I want select IPs from table those are in the same network by network address.

   ip_adress    |  netmask
----------------|-----------
 192.168.13.25  |    29    
 192.168.13.26  |    29
 192.168.13.1   |    30
 192.168.13.2   |    30

for example, I want IPs from table those are in the network address 192.168.13.24/29:

   ip_adress    |  netmask
----------------|-----------
 192.168.13.25  |    29    
 192.168.13.26  |    29

Upvotes: 0

Views: 1290

Answers (2)

Houari
Houari

Reputation: 5621

Using PostgreSQL's Network Address Functions and Operators you can easly get it with :

SELECT * FROM network_adresse_table 
WHERE network(cast(format('%1$s/%2$s',ip_adress,netmask) as inet)) = 
cast('192.168.13.24/29' as inet)

Here is an SQL Fiddle

Upvotes: 1

Wouter Verhelst
Wouter Verhelst

Reputation: 1292

PostgreSQL actually has a native inet type, meant for storing IP addresses with their netmask in a single field, along with specialized operators for matching those.

Example:

test=# \d inetdemo 
  Table "public.inetdemo"
 Column | Type | Modifiers 
--------+------+-----------
 addr   | inet | 

test=# select * from inetdemo;
       addr       
------------------
 192.168.13.25/29
 192.168.13.26/29
 192.168.13.1/30
 192.168.13.2/30
(4 rows)

test=# select * from inetdemo where addr >>= '192.168.13.25/29';
       addr       
------------------
 192.168.13.25/29
 192.168.13.26/29
(2 rows)

For the full story, this page explains the inet datatype (and its brother, cidr, which is not what you need), whereas this page explains the operators and functions you can use on inet and cidr data types. Take note of the masklen and host functions if you need to be able to get the ip address and/or netmask separately:

test=# select host(addr) as "ip_address", masklen(addr) as "netmask" from inetdemo;
  ip_address   | netmask 
---------------+---------
 192.168.13.25 |      29
 192.168.13.26 |      29
 192.168.13.1  |      30
 192.168.13.2  |      30
(4 rows)

Upvotes: 5

Related Questions