MIME-Version: 1.0
From: "Rhys A.D. Stewart" <rhys.stewart@gmail.com>
Date: Sat, 8 Mar 2025 14:01:04 -0500
Message-ID: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
Subject: exclusion constraint question
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CACg0vTnm2cxCmmkMutEL%2B%3D4hac0MnVgWykYFVndO8WV_WQMN8Q%40mail.gmail.com>
Precedence: bulk

Greetings All,

I have the following table:

CREATE TABLE shelves(
    shelf_id bigint PRIMARY KEY,
    l_mug_id bigint UNIQUE,
    c_mug_id bigint UNIQUE,
    r_mug_id bigint UNIQUE,
    CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id
<> r_mug_id),
    EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>, r_mug_id
WITH <>) -- Not working as expected (or my expectations are wrong).
);

And some data:

INSERT INTO shelves VALUES (1,    7,    2,    1);
INSERT INTO shelves VALUES (2,    3, null, null);
INSERT INTO shelves VALUES (3, null,    1,    4);
INSERT INTO shelves VALUES (4,    4,    5, null);

Mugs on shelves, fascinating. A mug_id can only appear once in the
entire table. The check constraint handles not having the same mug_id
in each row and the unique constraints does the same for the column.
But how do I get around checking multiple columns for the same mug_id.
I'm thinking an exclusion constraint, but (a) I do not know if I am
overthinking it and (b) the exclusion constraint I have does not work
as expected, or my expectations are way off.

Any suggestions would be appreciated.

Regards,

Rhys
Peace & Love | Live Long & Prosper