Re: exclusion constraint question

On 3/8/25 21:01, Rhys A.D. Stewart wrote:
> 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.

First i'd say look at the exclusion constraint and your unique keys.
Those are defined in a mutually-auto-canceling manner. e..g

insert mugs 10, 11, and 12 in shelf 3 :

insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id)
values(3,10,11,12);
INSERT 0 1

then insert mugs 13,14,15 in shelf 4 :

insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id)
values(4,13,14,15);
ERROR:  conflicting key value violates exclusion constraint
"shelves_l_mug_id_c_mug_id_r_mug_id_excl"
DETAIL:  Key (l_mug_id, c_mug_id, r_mug_id)=(13, 14, 15) conflicts with
existing key (l_mug_id, c_mug_id, r_mug_id)=(10, 11, 12).

in order to bypass the exclusion constraint one of the mags must match a
previous mug in the same position :

insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(4,13,14,12);

but then :

ERROR:  duplicate key value violates unique constraint
"shelves_r_mug_id_key"
DETAIL:  Key (r_mug_id)=(12) already exists.

So your exclusion constraint does not do what you would expect.

You can think of your mugs positions by pulling the shelves buckets in
order to make them look like they have a linear shape. Then you would
have a unique key on this linear store. What Laurenz said is most
probably correct.

hmm just thinking , would imagining making 64*3 long integers with all
three mug ids side by side ,and using bitwise operations along with
exclusion constraints might yield some results ?

>
> Any suggestions would be appreciated.
>
> Regards,
>
> Rhys
> Peace & Love | Live Long & Prosper
>
>