On Sat, 2025-03-08 at 14:01 -0500, Rhys A.D. Stewart wrote:
> 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.
I believe that this can only be solved by keeping a tally of the used
mugs in a second table that is maintained by a trigger.
Yours,
Laurenz Albe
