Subject: exclusion constraint question

From: "Rhys A.D. Stewart" <[email-masked]>
Date: Sat, 8 Mar 2025 14:01:04 -0500

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.

Any suggestions would be appreciated.

Regards,

Rhys
Peace & Love | Live Long & Prosper

---

From: [email-masked]
Date: Sat, 8 Mar 2025 20:26:44 +0100

Hey Rhys,

I think you might be better served doing something like

CREATE TABLE shelve_items(
	id bigint PRIMARY KEY,
	shelf_id bigint,
	column_name VARCHAR,
	mug_id bigint UNIQUE
)

and then putting a unique index on (shelf_id, column_name):

CREATE UNIQUE INDEX shelve_items_id_c_name on shelve_items (shelf_id, column_name)

The first row of your shelf would then look like:

INSERT INTO shelve_items VALUES (1, 1, left, 7)
INSERT INTO shelve_items VALUES (2, 1, middle, 2)
INSERT INTO shelve_items VALUES (3, 1, right, 1)

This would also allow you to scale to shelves that are n items wide (maybe at that point you could use an id for the columns instead of names)

Does this work for you?

Best,
Emanuel

> On 8 Mar 2025, at 20:01, Rhys A.D. Stewart <[email-masked]> wrote:
> 
> Greetings All,
>

---

From: Achilleas Mantzios - cloud <[email-masked]>
Date: Mon, 10 Mar 2025 10:43:37 +0200

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

---

From: Achilleas Mantzios - cloud <[email-masked]>
Date: Mon, 10 Mar 2025 11:52:47 +0200

On 3/10/25 10:43, Achilleas Mantzios - cloud wrote:
> 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 :
>
> values(4,13,14,12);
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.

---

From: "Rhys A.D. Stewart" <[email-masked]>
Date: Mon, 10 Mar 2025 17:28:39 -0500

Greetings,

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

I tried this and got the opclass error for the int8 and (since
postgres is so wonderfully extensible) considered trying to write the
oppclass for bigint. But ultimately writing the trigger as suggested
by Laurenz is much easier. So that is the route I went,

Thanks all for your input.

Rhys