Search in a range of ip's, PostgreSQL

Navigation
2

I'm doing a query to get ip addresses given a range, for example:

Obtain the ip's that are between 192.168.0.0 and 192.168.0.255

The query I'm using is: 

SELECT dir_ip
FROM nic
WHERE dir_ip
BETWEEN '192.168.0.0' AND '192.168.0.255'
ORDER BY dir_ip

The ip addresses are stored as text in the database, the problem with that query is that I do not know why it does not return all the ip addresses, which in total should be 256 and it only brings me 177 in total.

What query could you make to get all the ip's within the given range?

    
asked by Juan Pinzón 03.08.2016 в 22:27
source

2 answers

4

try the following query: 

SELECT dir_ip
FROM nic
WHERE CAST(dir_ip AS INET)
BETWEEN CAST('192.168.0.0' AS INET) AND CAST('192.168.0.255' AS INET)
ORDER BY CAST(dir_ip AS INET);

I hope it serves you.

    
answered by 03.08.2016 / 22:50
source
1

I'm just going to add a bit of information to Neyer's answer:

The "problem" is that as text field 192.168.0.3 is greater than 192.168.0.255, hence the search does not work for you.

Postgres has specific field types for this data, they are called Network Address , and among those types, inet is part of the response in question, this type of field accepts IPv4 or IPv6 addresses (and optionally its subnet). These types of field include check of data errors (input) and some special functions and operators:

link

    
answered by 03.08.2016 в 23:13
Search for repeated values in multidimensional array (PHP) How to manipulate svg with css?