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 1trtgs-00B1HV-VE for pgsql-general@arkaria.postgresql.org; Tue, 11 Mar 2025 07:06:15 +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 1trtgr-009Ua3-LL for pgsql-general@arkaria.postgresql.org; Tue, 11 Mar 2025 07:06:13 +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 1trtgr-009UYy-8M for pgsql-general@lists.postgresql.org; Tue, 11 Mar 2025 07:06:13 +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 1trtgn-002Bj1-12 for pgsql-general@lists.postgresql.org; Tue, 11 Mar 2025 07:06:12 +0000 Content-Type: multipart/alternative; boundary="------------Jkx2X3LUZwQvfDW2QzbpPVsf" Message-ID: Date: Tue, 11 Mar 2025 09:06:08 +0200 MIME-Version: 1.0 Subject: Re: exclusion constraint question To: "Rhys A.D. Stewart" Cc: 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: 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. --------------Jkx2X3LUZwQvfDW2QzbpPVsf Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 3/11/25 00:28, Rhys A.D. Stewart wrote: > 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, You have to install intarray extension. postgres@[local]/test=# 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 && ); ERROR:  data type integer[] has no default operator class for access method "gist" HINT:  You must specify an operator class for the index or define a default operator class for the data type. postgres@[local]/test=# postgres@[local]/test=# CREATE EXTENSION intarray ; CREATE EXTENSION postgres@[local]/test=# 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 && ); ALTER TABLE postgres@[local]/test=# insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(5,null,15,null); ERROR:  duplicate key value violates unique constraint "shelves_pkey" DETAIL:  Key (shelf_id)=(5) already exists. postgres@[local]/test=# select * from shelves ; shelf_id | l_mug_id | c_mug_id | r_mug_id ----------+----------+----------+----------        3 |       10 |       11 |       12        4 |       13 |       14 |       15        5 |          |       16 | (3 rows) postgres@[local]/test=# insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(6,15,null,null); ERROR:  conflicting key value violates exclusion constraint "shelves_excl" DETAIL:  Key (array_remove(ARRAY[l_mug_id::integer, c_mug_id::integer, r_mug_id::integer], NULL::integer))=({15}) conflicts with existing key (array_remove(ARRAY[l_mug_id::integer, c_mug_i d::integer, r_mug_id::integer], NULL::integer))=({13,14,15}). postgres@[local]/test=# is it still harder than the trigger ? > > Thanks all for your input. > > Rhys > Peace & Love | Live Long & Prosper --------------Jkx2X3LUZwQvfDW2QzbpPVsf Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 3/11/25 00:28, Rhys A.D. Stewart wrote:
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,

You have to install intarray extension.

postgres@[local]/test=# 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 && );
ERROR:  data type integer[] has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
postgres@[local]/test=#

postgres@[local]/test=# CREATE EXTENSION intarray ;
CREATE EXTENSION
postgres@[local]/test=# 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 && );
ALTER TABLE
postgres@[local]/test=# insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(5,null,15,null);
ERROR:  duplicate key value violates unique constraint "shelves_pkey"
DETAIL:  Key (shelf_id)=(5) already exists.
postgres@[local]/test=# select * from shelves ;
shelf_id | l_mug_id | c_mug_id | r_mug_id  
----------+----------+----------+----------
       3 |       10 |       11 |       12
       4 |       13 |       14 |       15
       5 |          |       16 |          
(3 rows)

postgres@[local]/test=# insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(6,15,null,null);
ERROR:  conflicting key value violates exclusion constraint "shelves_excl"
DETAIL:  Key (array_remove(ARRAY[l_mug_id::integer, c_mug_id::integer, r_mug_id::integer], NULL::integer))=({15}) conflicts with existing key (array_remove(ARRAY[l_mug_id::integer, c_mug_i
d::integer, r_mug_id::integer], NULL::integer))=({13,14,15}).
postgres@[local]/test=#


is it still harder than the trigger ?


Thanks all for your input.

Rhys
Peace & Love | Live Long & Prosper
--------------Jkx2X3LUZwQvfDW2QzbpPVsf--