Re: exclusion constraint question

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(at)[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(at)[local]/test=#

postgres(at)[local]/test=# CREATE EXTENSION intarray ;
CREATE EXTENSION
postgres(at)[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(at)[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(at)[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(at)[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(at)[local]/test=#

is it still harder than the trigger ?

>
> Thanks all for your input.
>
> Rhys
> Peace & Love | Live Long & Prosper