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 10:43:37 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
References: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>


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

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