MIME-Version: 1.0
References: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
 <6f7fff13-fbc6-423b-b8cb-73762b0cc28b@cloud.gatewaynet.com> <efd95608-75cf-4b38-9b05-8600c5a2a791@cloud.gatewaynet.com>
In-Reply-To: <efd95608-75cf-4b38-9b05-8600c5a2a791@cloud.gatewaynet.com>
From: "Rhys A.D. Stewart" <rhys.stewart@gmail.com>
Date: Mon, 10 Mar 2025 17:28:39 -0500
Message-ID: <CACg0vTkWs2huWDyp6EJHcsAcdcDSyo6CRMD36z4sr7S7ZY-8Qg@mail.gmail.com>
Subject: Re: exclusion constraint question
To: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CACg0vTkWs2huWDyp6EJHcsAcdcDSyo6CRMD36z4sr7S7ZY-8Qg%40mail.gmail.com>
Precedence: bulk

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
Peace & Love | Live Long & Prosper