Search code examples
sqlpostgresqlip

Postgresql query fo find IP address

I'm trying to find an IP address that match a range of hosts (172.24.12.???), but none of the following queries are working:

select * from pg_catalog.pg_stat_activity 
--where client_addr <> E'(?|172\.24\.12\.)'::inet;
--where client_addr <> E'(://|^)172\\.24\\.12\\.[0-9]'::inet

I'm getting two different errors. SQL Error [22P02]: ERROR: invalid input syntax for type inet: "(?|172.24.12.)" and SQL Error [22P02]: ERROR: invalid input syntax for type inet: "(^)172.24.12.[0-9]"

What Am I doing wrong here. Thanks!

Solution

  • PostgreSQL has native utilities to handle IP addresses, you don't need to use string manipulation as workaround:

    WHERE client_addr << '172.24.12/24'

    Demo code:

    WITH fake_pg_stat_activity (client_addr) AS (
    SELECT inet '172.24.12.20'
    UNION ALL SELECT inet '192.168.0.1'
)
SELECT *, CASE WHEN client_addr << '172.24.12/24' THEN TRUE ELSE FALSE END AS belongs_to_subnet
FROM fake_pg_stat_activity;