Postgresql inet find duplicate / overlapping network entries-CodePudding

I've got the following table structure for storing IPs (PostgreSQL 11.14):

CREATE TABLE ips (
  ip INET 
);

INSERT INTO ips VALUES ('10.0.0.4');
INSERT INTO ips VALUES ('10.0.0.0/24');
INSERT INTO ips VALUES ('10.1.0.0/23');
INSERT INTO ips VALUES ('10.1.0.0/27');

I need to know which network range is duplicate to find overlapping network entries.

CodePudding user response：

inet && inet → boolean operator tells you if there's an overlap. It's commutative, so you can apply an exclusion constraint on that table to block incoming overlapping entries:

CREATE TABLE ips (
  ip INET,
  constraint no_ip_overlaps exclude using gist (ip inet_ops WITH &&));

INSERT INTO ips (ip) 
VALUES ('10.0.0.4'),
       ('10.1.0.0/27');
-- You can let the unhandled conflict throw an error
INSERT INTO ips (ip) VALUES ('10.0.0.0/24');
--ERROR:  conflicting key value violates exclusion constraint "no_ip_overlaps"
--DETAIL:  Key (ip)=(10.0.0.0/24) conflicts with existing key (ip)=(10.0.0.4).

You can decide to handle the conflicts as they come, either by ignoring them or being selective:

INSERT INTO ips (ip) VALUES ('10.0.0.0/24')
  on conflict on constraint no_ip_overlaps do nothing;

--You might one day decide to keep the bigger network in the overlapping pair: 
--right now, only 'do nothing' is supported for conflicts on exclusion constraints
INSERT INTO ips (ip) VALUES ('10.1.0.0/23') 
  on conflict on constraint no_ip_overlaps do update 
    set ip=case when ips.ip<<excluded.ip then excluded.ip else ips.ip end;
--ERROR:  ON CONFLICT DO UPDATE not supported with exclusion constraints

--Until that day you can revert to a MERGE in place of your INSERT
MERGE INTO ips as present
USING (SELECT '10.1.0.0/23'::inet as ip) AS incoming 
  ON (present.ip << incoming.ip)
WHEN MATCHED THEN UPDATE 
  SET ip=incoming.ip
WHEN NOT MATCHED THEN 
  INSERT (ip)
  VALUES (incoming.ip);

Since MERGE has only recently been added to PostgreSQL 15, on earlier versions you can get away with a PL/pgSQL upsert.

To detect already existing overlaps, you can use inet <<= inet → boolean instead, to avoid listing both addresses of each overlapping pair, as suggested by @a_horse_with_no_name.

CREATE TABLE ips (
  id serial primary key,
  ip INET 
);

INSERT INTO ips 
  (ip) 
VALUES 
  ('10.0.0.4'),
  ('10.0.0.0/24'),
  ('10.1.0.0/23'),
  ('10.1.0.0/27');
create index on ips using gist(ip inet_ops,id);

select 
  a.id as id1, 
  a.ip as ip1,
  b.id as id2,
  b.ip as ip2 
from ips a 
  inner join ips b 
    on a.ip <<= b.ip 
    and a.id<>b.id;

-- id1 |     ip1     | id2 |     ip2
------- ------------- ----- -------------
--   1 | 10.0.0.4    |   2 | 10.0.0.0/24
--   4 | 10.1.0.0/27 |   3 | 10.1.0.0/23
--(2 rows)

CodePudding user response：

We can use SUBSTRING() here along with a regex pattern:

WITH cte AS (
    SELECT *, COUNT(*) OVER (PARTITION BY SUBSTRING(ip::text FROM '[^/] ')) cnt
    FROM ips
)

SELECT *
FROM cte
WHERE cnt > 1;