我有以下用于存盤 IP 的表結構（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');
我需要知道哪個網路范圍是重復的以查找重疊的網路條目。
uj5u.com熱心網友回復：
inet && inet → boolean運營商會告訴您是否有重疊。它是可交換的，因此您可以在該表上應用排除約束來阻止傳入的重疊條目：
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).
您可以決定在沖突發生時處理它們，要么忽略它們，要么選擇性地處理它們：
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);
由于MERGE最近才被添加到 PostgreSQL 15中，因此在早期版本中，您可以使用PL/pgSQL upsert。
要檢測已經存在的重疊，您可以改用@a_horse_with_no_name的建議來inet <<= inet → boolean避免列出每個重疊對的兩個地址。
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)
uj5u.com熱心網友回復：
我們可以SUBSTRING()在這里使用正則運算式模式：
WITH cte AS (
SELECT *, COUNT(*) OVER (PARTITION BY SUBSTRING(ip::text FROM '[^/] ')) cnt
FROM ips
)
SELECT *
FROM cte
WHERE cnt > 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/519447.html