By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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')
returning *;
-- You can let the unhandled conflict throw an error
CREATE TABLE
| ip |
|---|
| 10.0.0.4 |
| 10.1.0.0/27 |
INSERT 0 2
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
returning *;
| ip |
|---|
INSERT 0 0
--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
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 1