Postgres Check if IP (inet) is IN a list of IP Ranges

Question

I want to check if an IP exists in a range of ranges, eg: SELECT * FROM ip_address WHERE ip IN (<list of ip ranges>)

Postgresql documentation states to use the << operator to check if an IP is contained within a single IP Range, eg: inet '192.168.1.5' << inet '192.168.1/24', but I'm not sure how to use it on a list of ranges without having to construct an OR chain of <<'s.

score 17 · Accepted Answer · answered Jul 24 '14 at 21:21

17

select inet '192.168.1.5' << any (array['192.168.1/24', '10/8']::inet[]);
 ?column? 
----------
 t

http://www.postgresql.org/docs/current/static/functions-comparisons.html#AEN18486

answered Jul 24 '14 at 21:21

Clodoaldo Neto

118,695
26
233
260

2

Note that if the list can also contain exact non-range matches like '192.168.1.1', you should use `<<=` (contains or equals) rather than `<<` (contains). This caught me out. – Henrik N Sep 26 '22 at 11:46

Postgres Check if IP (inet) is IN a list of IP Ranges

1 Answers1

Linked