Search code examples
sqlpostgresqlip-addresswhere-clause

Postgres "is not contained by" operator for IP addresses or inet types

Postgres has an operator for determining whether an IP address is contained by a given range, for example:

SELECT * FROM clients WHERE ip_address <<= inet '10.0.0.0/16';

How could I filter for the reverse set, the set of addresses not in that subnet? The documentation of net functions does not seem to have an operator that can do this. https://www.postgresql.org/docs/12/functions-net.html

Solution

  • Use NOT:

    SELECT * 
FROM clients 
WHERE NOT (ip_address <<= inet '10.0.0.0/16');

    Parentheses are not necessary, inet operators have higher precedence than boolean ones.