public inbox for [email protected]  

help / color / mirror / Atom feed

From: Achilleas Mantzios - cloud <[email protected]>
To: [email protected]
Subject: Re: exclusion constraint question
Date: Mon, 10 Mar 2025 11:52:47 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
	<[email protected]>


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
>>
>>

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: exclusion constraint question
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox