Re: exclusion constraint question

Achilleas Mantzios - cloud <a.mantzios@xxxxxxxxxxxxxxxxxxxx> · Tue, 11 Mar 2025 09:06:08 +0200

    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