Postgresql query fo find IP address-CodePudding

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!

CodePudding user response：

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;

CodePudding user response：

To answer this, I did the following (all of the code below is available on the fiddle here):

CREATE TABLE test
(
  IP INET
);

and

INSERT INTO test VALUES
('134.34.34.34'::INET),
('172.24.12.20'::INET);

Now, you appear to have your IP addresses as strings. This is not the best idea - it's always best to use the appropriate data type (operators, comparisons, sorting, indexing, correct values enforced automatically), but in this case, we'll just have to use strings.

As pointed out by @ÁlvaroGonzález, this works nicely with IP addresses:

SELECT 
  *
FROM test
WHERE ip <<= '172.24.12/24'::INET;

Result:

ip
172.24.12.20

We'll just have to use the PostgreSQL cast operator (::) to convert these to strings as follows:

SELECT
  ip
FROM test
WHERE ip::TEXT ~ '172\.24\.12\.[0-2]{1}[0-9]{1,2}'

Result:

ip
172.24.12.20

The regex above isn't the best - you could spend all day searching for regexes - for example this:

SELECT
  ip
FROM test
WHERE ip::TEXT ~ '172\.24\.12\.([0-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])';

will also work and is more thorough. It's up to you to choose which regex covers your needs.