Question 90563 in PostgreSQL Question posted in
The official documentation can be found here.

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!

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search