Postgresql inet find duplicate / overlapping network entries

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.

To detect already existing overlaps, you can use inet <<= inet → boolean, similarly to what @a_horse_with_no_name suggested: demo

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 AS a 
  INNER JOIN ips AS b 
    ON a.ip <<= b.ip 
    AND a.id<>b.id;

If you wish to prevent overlaps from being inserted, you can us a commutative inet && inet → boolean operator in an exclusion constraint on that table: demo

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 but 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.