[2024-06-07] Exclusion constraints as "UNIQUE except for X"

example.sql

CREATE TABLE foo (
  id INTEGER PRIMARY KEY,
  v1 INTEGER NOT NULL,
  v2 INTEGER NOT NULL,
  v3 INTEGER NOT NULL
);

ALTER TABLE foo
  ADD CONSTRAINT uniq_except_v3_zero
  EXCLUDE USING GIST (v1 WITH =, v2 WITH =, v3 WITH =) WHERE (v3 <> 0);

INSERT INTO foo (id, v1, v2, v3) VALUES (1, 10, 11, 12), (2, 20, 30, 40);
-- INSERT 0 2
-- Time: 0.005s

SELECT * FROM foo;
-- +----+----+----+----+
-- | id | v1 | v2 | v3 |
-- |----+----+----+----|
-- | 1  | 10 | 11 | 12 |
-- | 2  | 20 | 30 | 40 |
-- +----+----+----+----+
-- SELECT 2
-- Time: 0.009s

INSERT INTO foo (id, v1, v2, v3) VALUES (3, 10, 11, 12);
-- conflicting key value violates exclusion constraint "uniq_except_v3_zero"
-- DETAIL:  Key (v1, v2, v3)=(10, 11, 12) conflicts with existing key (v1, v2, v3)=(10, 11, 12).
-- Time: 0.010s

INSERT INTO foo (id, v1, v2, v3) VALUES (3, 10, 11, 13);
-- INSERT 0 1
-- Time: 0.003s

INSERT INTO foo (id, v1, v2, v3) VALUES (4, 100, 200, 0), (5, 100, 200, 0);
-- INSERT 0 2
-- Time: 0.005s

SELECT * FROM foo;
-- +----+-----+-----+----+
-- | id | v1  | v2  | v3 |
-- |----+-----+-----+----|
-- | 1  | 10  | 11  | 12 |
-- | 2  | 20  | 30  | 40 |
-- | 3  | 10  | 11  | 13 |
-- | 4  | 100 | 200 | 0  |
-- | 5  | 100 | 200 | 0  |
-- +----+-----+-----+----+
-- SELECT 5
-- Time: 0.009s