public inbox for [email protected]
help / color / mirror / Atom feedFrom: Achilleas Mantzios - cloud <[email protected]>
To: Rhys A.D. Stewart <[email protected]>
Cc: [email protected]
Subject: Re: exclusion constraint question
Date: Tue, 11 Mar 2025 09:06:08 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACg0vTkWs2huWDyp6EJHcsAcdcDSyo6CRMD36z4sr7S7ZY-8Qg@mail.gmail.com>
References: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<CACg0vTkWs2huWDyp6EJHcsAcdcDSyo6CRMD36z4sr7S7ZY-8Qg@mail.gmail.com>
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: exclusion constraint question
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox