Help with cidr data type?

[DCM]

Hi i have a table which contains IP networks stored as a cidr data type in postgreSQL.

 

I have read the offical support page on both inet and cidr but I cannot find a simple practical example where this data type is queried in SQL statement, at least not to achieve the below?

 

if for example the table was as follows:

 

Network_Name  |    Network_Address

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

Network A        |    192.168.1.0/24

Network B        |    192.168.2.0/24

Network C        |    192.168.3.0/24

Network D        |    10.1.1.0/24

Network E        |    10.1.2.0/24

Network F        |    10.1.3.0/24

 

How can i construct an SQL query querying data stored in the Network_Address that matches/contains the users search criteria of say '192.168.': I would like this to return all the 192.168.x.x networks.

 

This seems to work with the inet type as it lets you use the LIKE command but cidr will not allow this.

 

If i type in the full Network address is works, but i need to be able to do partial searches.

 

I would also like to be able to do more advanced things such as checking if a network already exists or overlaps/conflicts with current assigned networks.

 

From what i have read i think i may need to do something called casting with the functions available to the cidr data type but i'm not really sure how to go about it.

 

Thanks for reading, any pointers are appreciated

 

 

 

 

[DCM]

I think i have solved my first query by casting the contents to TEXT as below so i can now do partial queries:

 

SELECT * FROM network_table WHERE network::TEXT LIKE '%192.%';

 

I would still however really appreciate a practical example of how some of the more advanced cidr functions can be used to for example:

 

1> Determine if a network exists.

 

2> If a new network allocation falls within the range of an already existing network. If the network 192.168.1.0/24 exists then i would like to be able to detect that allocating 192.168.1.16/28 would cause a conflict.