Re: exclusion constraint question

On 3/10/25 10:43, Achilleas Mantzios - cloud wrote:
>
>
> 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 ?
>
I think I got it :

ALTER TABLE shelves ADD CONSTRAINT shelves_excl EXCLUDE USING gist(
 array_remove((ARRAY[l_mug_id,c_mug_id,r_mug_id]::int[]),NULL) WITH && );

but the caveat is I had to cast to 32-bit int array. Haven't found any
opclass for int8  (bigint) in intarray extension.

The idea is make sure that there is no overlap between elements in every
two different rows.

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