Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1trZof-005k2P-DT for pgsql-general@arkaria.postgresql.org; Mon, 10 Mar 2025 09:52:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1trZoe-0081Ry-19 for pgsql-general@arkaria.postgresql.org; Mon, 10 Mar 2025 09:52:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1trZod-0081Qv-LY for pgsql-general@lists.postgresql.org; Mon, 10 Mar 2025 09:52:55 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1trZoZ-002120-2l for pgsql-general@lists.postgresql.org; Mon, 10 Mar 2025 09:52:55 +0000 Content-Type: multipart/alternative; boundary="------------QQsUC7tLG4u0Eq00cv6yrzHb" Message-ID: Date: Mon, 10 Mar 2025 11:52:47 +0200 MIME-Version: 1.0 Subject: Re: exclusion constraint question To: pgsql-general@lists.postgresql.org References: <6f7fff13-fbc6-423b-b8cb-73762b0cc28b@cloud.gatewaynet.com> Content-Language: en-US From: Achilleas Mantzios - cloud In-Reply-To: <6f7fff13-fbc6-423b-b8cb-73762b0cc28b@cloud.gatewaynet.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------QQsUC7tLG4u0Eq00cv6yrzHb Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 >> >> --------------QQsUC7tLG4u0Eq00cv6yrzHb Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


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