slvgka
Last Updated: March 06, 2017
·
38
· mroach_
16107140 10100651592874953 393703796820975529 o

PostgreSQL: Exclude private IPs from query

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'
)
Say Thanks
Respond