In PostgreSQL,
I have a column named as ‘IP’ whose Datatype is Array. I want to filter the records where IP are between the particular range. I tried below queries but throwing error in postgresql.
Select * from table1 where IP BETWEEN 10.2.0.0 AND 10.15.0.8
Select * from table1 where IP > 10.2.0.0 AND IP < 10.15.0.8
Select * from table1 where IP::inet < '127.0.0.1'::inet
None of the above queries are working since datatype of IP column is String or String Array
None of the above queries are working since datatype of IP column is String or String Array.
Either the datatype is String or Array, How to compare and filter the records?
I think this can work
WITH expanded_ips AS ( SELECT id, unnest(IP) AS ip_address FROM your_table), filtered_ips AS ( SELECT id FROM expanded_ips WHERE inet(ip_address) >= inet('192.168.1.1')::inet AND inet(ip_address) <= inet('192.168.1.255')::inet);
SELECT DISTINCT yt.* FROM your_table yt JOIN filtered_ips fi ON yt.id = fi.id;
When I run the below query in pgadmin it is throwing error at(::inet)
Query:
select principal_ip
from
event , unnest(principal.ip) as principal_ip
WHERE inet(principal_ip) >= inet(‘192.168.1.1’)::inet
Error:
Syntax error: Expected end of input but got “:”
Hemavathy A is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.