Let's say you have a table of security events. Logins, password changes, etc, and you log the user's ip. Then you have a scheduled task to go lookup the geoip information for each record. You wouldn't want to waste resources querying non-routable IP addresses. So, you can exclude them! This query assumes you're using the
INET column type. If you're not you could cast the column like
ip::inet. This only handles IPv4 addresses.
SELECT * FROM audit_security_events WHERE NOT ( ip << '127.0.0.0/8' OR ip << '10.0.0.0/8' OR ip << '172.16.0.0/12' OR ip << '192.168.0.0/16' )