Last Updated: March 06, 2017
· mroach_

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.

FROM audit_security_events
    ip << '' OR
    ip << '' OR
    ip << '' OR
    ip << ''