1

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!

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
Marcio Lino
  • 379
  • 2
  • 15
  • You should not use `(?|`, it is a PCRE construct, not supported in PostgreSQL regex. Replace with `(?:`, or remove the grouping if you only have one branch. Also, `~` is the regex matching operator, not `<>`. Try `where client_addr ~ '([^0-9]|^)172\.24\.12\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)([^0-9]|$)'` – Wiktor Stribiżew Dec 13 '22 at 10:52
  • Hi Wiktor! It doesn't did the tick. I got the following error: Operator does not exist: inet ~ unknown Tip: No operator matches the given name and argument type(s). You might need to add explicit type casts. – Marcio Lino Dec 13 '22 at 11:09

2 Answers2

3

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;
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
0

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.

Vérace
  • 854
  • 10
  • 41