Prevent overlapping values on CIDR column in PostgreSQL

Question

Is there a constraint or some other PostgreSQL feature that prevents CIDR columns from having values that overlap?

For example:

192.168.1.0/24 and 192.168.1.1/32

These could not exist together because 192.168.1.1/32 is contained in the 192.168.1.0/24 subnet.

Modest

Answer 1

Yes, that is easily done with an exclusion constraint.

CREATE TABLE networks (
   id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   net cidr NOT NULL
);

ALTER TABLE networks ADD EXCLUDE USING gist (net inet_ops WITH &&);

INSERT INTO networks (net) VALUES ('192.168.1.0/24');
INSERT 0 1

INSERT INTO networks (net) VALUES ('192.168.1.1/32');
ERROR:  conflicting key value violates exclusion constraint "networks_net_excl"
DETAIL:  Key (net)=(192.168.1.1) conflicts with existing key (net)=(192.168.1.0/24).

The exclusion constraint will cause an error whenever you try to insert two rows where the values for net overlap (&& is the "overlaps" operator).

Lelahleland

Is it possible to limit the constraint based on the value of another field? E.g. I would like to validate that CIDRs do not overlap for a specific VLAN but they can overlap between different vlans. Thank you – Guncotton

@Guncotton Sure, just add the other column to the exclusion constraint. You may need the btree_gist extension to use = in an exclusion constraint. – Lelahleland

it'd be amazing if you could explain what is going on here. – Chaing

@Chaing I have added an explanation. – Lelahleland