PostgreSQL中如何查找inet类型的重复与重叠网络条目？

PostgreSQL的INET类型内置了专门的操作符，可直接用来判断IP地址或子网之间的包含、重叠关系，利用这些操作符就能快速找出表中存在重叠的条目。

基础查询语句 #

以下查询会找出所有存在包含或重叠关系的IP条目对（排除自身与自身的无效对比）：

SELECT a.ip AS ip1, b.ip AS ip2
FROM ips a
JOIN ips b ON a.ip <> b.ip 
AND (a.ip <<= b.ip OR b.ip <<= a.ip OR a.ip && b.ip)
ORDER BY a.ip, b.ip;

语句说明 #

• a.ip <> b.ip：排除条目与自身的无效对比
• a.ip <<= b.ip：判断ip1是否完全被ip2的子网包含（比如单个IP属于某个子网）
• b.ip <<= a.ip：判断ip2是否完全被ip1的子网包含（比如小子网属于大子网）
• a.ip && b.ip：判断两个IP范围是否存在部分重叠（既不是完全包含，又有交集）

优化查询（避免重复配对） #

上面的查询会出现反向配对（比如ip1=10.0.0.4, ip2=10.0.0.0/24和ip1=10.0.0.0/24, ip2=10.0.0.4），可以用a.ip < b.ip替换a.ip <> b.ip，让每个配对只显示一次：

SELECT a.ip AS ip1, b.ip AS ip2
FROM ips a
JOIN ips b ON a.ip < b.ip 
AND (a.ip <<= b.ip OR b.ip <<= a.ip OR a.ip && b.ip)
ORDER BY a.ip, b.ip;

针对测试数据的结果 #

用你提供的测试数据运行优化后的查询，会得到以下结果：

ip1       |       ip2
----------------+----------------
 10.0.0.4       | 10.0.0.0/24
 10.1.0.0/27    | 10.1.0.0/23

内容的提问来源于stack exchange，提问作者kaepchenrot