Example 1 – Prevent overlapping reservations (time ranges)
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_reservations (
    room_id INT,
    period  tstzrange,
    guest   TEXT
);

ALTER TABLE room_reservations
ADD CONSTRAINT no_room_overlap
EXCLUDE USING gist (
    room_id WITH =,
    period  WITH &&
);

INSERT INTO room_reservations VALUES
(101, '[2026-02-14 10:00, 2026-02-14 11:00)', 'Alice'),
(101, '[2026-02-14 11:00, 2026-02-14 12:00)', 'Bob');

-- ❌ rejected (overlap)
INSERT INTO room_reservations
VALUES (101, '[2026-02-14 10:30, 2026-02-14 11:30)', 'Charlie');

Example 2 – Only one ACTIVE row (no range type)
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE user_roles (
    user_id INT,
    role    TEXT,
    status  TEXT
);

ALTER TABLE user_roles
ADD CONSTRAINT one_active_role
EXCLUDE USING gist (
    user_id WITH =,
    role    WITH =
)
WHERE (status = 'ACTIVE');

INSERT INTO user_roles VALUES
(1, 'ADMIN', 'ACTIVE'),
(1, 'ADMIN', 'INACTIVE'),
(1, 'ADMIN', 'DISABLED');

-- ❌ rejected (second ACTIVE)
INSERT INTO user_roles VALUES (1, 'ADMIN', 'ACTIVE');

Example 3 – Update causing violation
-- ❌ rejected if an ACTIVE row already exists
UPDATE user_roles
SET status = 'ACTIVE'
WHERE user_id = 1 AND role = 'ADMIN';

Example 4 – Prevent overlapping date ranges (daterange)
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE contracts (
    customer_id INT,
    valid_period daterange
);

ALTER TABLE contracts
ADD CONSTRAINT no_contract_overlap
EXCLUDE USING gist (
    customer_id  WITH =,
    valid_period WITH &&
);

INSERT INTO contracts VALUES
(10, '[2025-01-01,2025-06-01)'),
(10, '[2025-06-01,2025-12-31)');

-- ❌ rejected
INSERT INTO contracts
VALUES (10, '[2025-05-01,2025-07-01)');

Example 5 – ACTIVE-only overlap prevention
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE reservations (
    resource_id INT,
    period      daterange,
    status      TEXT
);

ALTER TABLE reservations
ADD CONSTRAINT active_only_no_overlap
EXCLUDE USING gist (
    resource_id WITH =,
    period      WITH &&
)
WHERE (status = 'ACTIVE');

INSERT INTO reservations VALUES
(1, '[2025-01-01,2025-01-10)', 'ACTIVE'),
(1, '[2025-01-05,2025-01-15)', 'CANCELLED');

-- ❌ rejected (ACTIVE overlap)
INSERT INTO reservations
VALUES (1, '[2025-01-05,2025-01-15)', 'ACTIVE');

Example 6 – IP network overlap (CIDR, no range type)
CREATE TABLE ip_allocations (
    network CIDR,
    owner   TEXT
);

ALTER TABLE ip_allocations
ADD CONSTRAINT no_network_overlap
EXCLUDE USING gist (
    network WITH &&
);

INSERT INTO ip_allocations VALUES
('192.168.1.0/24', 'Team A');

-- ❌ rejected
INSERT INTO ip_allocations
VALUES ('192.168.1.128/25', 'Team B');

Example 7 – Spatial overlap (BOX)
CREATE TABLE zones (
    zone_id INT,
    area    BOX
);

ALTER TABLE zones
ADD CONSTRAINT no_zone_overlap
EXCLUDE USING gist (
    area WITH &&
);

INSERT INTO zones VALUES
(1, box '(0,0),(10,10)');

-- ❌ rejected
INSERT INTO zones
VALUES (2, box '(5,5),(15,15)');

Example 8 – Equality-only exclusion (smart UNIQUE)
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE emails (
    email  TEXT,
    active BOOLEAN
);

ALTER TABLE emails
ADD CONSTRAINT one_active_email
EXCLUDE USING gist (
    email  WITH =,
    active WITH =
)
WHERE (active = TRUE);

INSERT INTO emails VALUES
('a@test.com', TRUE),
('a@test.com', FALSE);

-- ❌ rejected
INSERT INTO emails VALUES ('a@test.com', TRUE);