Thread: exclusion constraint question

exclusion constraint question

From

"Rhys A.D. Stewart"

Date:

08 March, 22:01:04

Greetings All,

I have the following table:

CREATE TABLE shelves(
    shelf_id bigint PRIMARY KEY,
    l_mug_id bigint UNIQUE,
    c_mug_id bigint UNIQUE,
    r_mug_id bigint UNIQUE,
    CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id
<> r_mug_id),
    EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>, r_mug_id
WITH <>) -- Not working as expected (or my expectations are wrong).
);

And some data:

INSERT INTO shelves VALUES (1,    7,    2,    1);
INSERT INTO shelves VALUES (2,    3, null, null);
INSERT INTO shelves VALUES (3, null,    1,    4);
INSERT INTO shelves VALUES (4,    4,    5, null);

Mugs on shelves, fascinating. A mug_id can only appear once in the
entire table. The check constraint handles not having the same mug_id
in each row and the unique constraints does the same for the column.
But how do I get around checking multiple columns for the same mug_id.
I'm thinking an exclusion constraint, but (a) I do not know if I am
overthinking it and (b) the exclusion constraint I have does not work
as expected, or my expectations are way off.

Any suggestions would be appreciated.

Regards,

Rhys
Peace & Love | Live Long & Prosper

Re: exclusion constraint question

From

personal@emanuelseemann.ch

Date:

08 March, 22:26:44

Hey Rhys,

I think you might be better served doing something like

CREATE TABLE shelve_items(
    id bigint PRIMARY KEY,
    shelf_id bigint,
    column_name VARCHAR,
    mug_id bigint UNIQUE
)

and then putting a unique index on (shelf_id, column_name):

CREATE UNIQUE INDEX shelve_items_id_c_name on shelve_items (shelf_id, column_name)

The first row of your shelf would then look like:

INSERT INTO shelve_items VALUES (1, 1, left, 7)
INSERT INTO shelve_items VALUES (2, 1, middle, 2)
INSERT INTO shelve_items VALUES (3, 1, right, 1)

This would also allow you to scale to shelves that are n items wide (maybe at that point you could use an id for the
columnsinstead of names) 

Does this work for you?

Best,
Emanuel

> On 8 Mar 2025, at 20:01, Rhys A.D. Stewart <rhys.stewart@gmail.com> wrote:
>
> Greetings All,
>
> I have the following table:
>
> CREATE TABLE shelves(
>    shelf_id bigint PRIMARY KEY,
>    l_mug_id bigint UNIQUE,
>    c_mug_id bigint UNIQUE,
>    r_mug_id bigint UNIQUE,
>    CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id
> <> r_mug_id),
>    EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>, r_mug_id
> WITH <>) -- Not working as expected (or my expectations are wrong).
> );
>
> And some data:
>
> INSERT INTO shelves VALUES (1,    7,    2,    1);
> INSERT INTO shelves VALUES (2,    3, null, null);
> INSERT INTO shelves VALUES (3, null,    1,    4);
> INSERT INTO shelves VALUES (4,    4,    5, null);
>
> Mugs on shelves, fascinating. A mug_id can only appear once in the
> entire table. The check constraint handles not having the same mug_id
> in each row and the unique constraints does the same for the column.
> But how do I get around checking multiple columns for the same mug_id.
> I'm thinking an exclusion constraint, but (a) I do not know if I am
> overthinking it and (b) the exclusion constraint I have does not work
> as expected, or my expectations are way off.
>
> Any suggestions would be appreciated.
>
> Regards,
>
> Rhys
> Peace & Love | Live Long & Prosper
>
>

Re: exclusion constraint question

From

"David G. Johnston"

Date:

08 March, 22:34:28

On Sat, Mar 8, 2025 at 12:01 PM Rhys A.D. Stewart <rhys.stewart@gmail.com> wrote:

CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id
<> r_mug_id),
EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>, r_mug_id
WITH <>) -- Not working as expected (or my expectations are wrong).
);

And some data:

INSERT INTO shelves VALUES (1, 7, 2, 1);
INSERT INTO shelves VALUES (2, 3, null, null);
INSERT INTO shelves VALUES (3, null, 1, 4);
INSERT INTO shelves VALUES (4, 4, 5, null);

Any suggestions would be appreciated.

Using "equals" or "not equals" when one of the inputs can be null is not usually what you want to do. The comparison evaluates to NULL which the constraint allows.

David J.

Re: exclusion constraint question

From

Laurenz Albe

Date:

08 March, 23:26:09

On Sat, 2025-03-08 at 14:01 -0500, Rhys A.D. Stewart wrote:
> I have the following table:
>
> CREATE TABLE shelves(
>     shelf_id bigint PRIMARY KEY,
>     l_mug_id bigint UNIQUE,
>     c_mug_id bigint UNIQUE,
>     r_mug_id bigint UNIQUE,
>     CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id
> <> r_mug_id),
>     EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>, r_mug_id
> WITH <>) -- Not working as expected (or my expectations are wrong).
> );
>
> And some data:
>
> INSERT INTO shelves VALUES (1,    7,    2,    1);
> INSERT INTO shelves VALUES (2,    3, null, null);
> INSERT INTO shelves VALUES (3, null,    1,    4);
> INSERT INTO shelves VALUES (4,    4,    5, null);
>
> Mugs on shelves, fascinating. A mug_id can only appear once in the
> entire table. The check constraint handles not having the same mug_id
> in each row and the unique constraints does the same for the column.
> But how do I get around checking multiple columns for the same mug_id.
> I'm thinking an exclusion constraint, but (a) I do not know if I am
> overthinking it and (b) the exclusion constraint I have does not work
> as expected, or my expectations are way off.
>
> Any suggestions would be appreciated.

I believe that this can only be solved by keeping a tally of the used
mugs in a second table that is maintained by a trigger.

Yours,
Laurenz Albe

Re: exclusion constraint question

From

Achilleas Mantzios - cloud

Date:

10 March, 11:43:37

On 3/8/25 21:01, Rhys A.D. Stewart wrote:

Greetings All,

I have the following table:

CREATE TABLE shelves(    shelf_id bigint PRIMARY KEY,    l_mug_id bigint UNIQUE,    c_mug_id bigint UNIQUE,    r_mug_id bigint UNIQUE,    CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id
<> r_mug_id),    EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>, r_mug_id
WITH <>) -- Not working as expected (or my expectations are wrong).
);

And some data:

INSERT INTO shelves VALUES (1,    7,    2,    1);
INSERT INTO shelves VALUES (2,    3, null, null);
INSERT INTO shelves VALUES (3, null,    1,    4);
INSERT INTO shelves VALUES (4,    4,    5, null);

Mugs on shelves, fascinating. A mug_id can only appear once in the
entire table. The check constraint handles not having the same mug_id
in each row and the unique constraints does the same for the column.
But how do I get around checking multiple columns for the same mug_id.
I'm thinking an exclusion constraint, but (a) I do not know if I am
overthinking it and (b) the exclusion constraint I have does not work
as expected, or my expectations are way off.

First i'd say look at the exclusion constraint and your unique keys. Those are defined in a mutually-auto-canceling manner. e..g

insert mugs 10, 11, and 12 in shelf 3 :

insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(3,10,11,12);
INSERT 0 1

then insert mugs 13,14,15 in shelf 4 :

insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(4,13,14,15);
ERROR: conflicting key value violates exclusion constraint "shelves_l_mug_id_c_mug_id_r_mug_id_excl"
DETAIL: Key (l_mug_id, c_mug_id, r_mug_id)=(13, 14, 15) conflicts with existing key (l_mug_id, c_mug_id, r_mug_id)=(10, 11, 12).

in order to bypass the exclusion constraint one of the mags must match a previous mug in the same position :

insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(4,13,14,12);

but then :

ERROR: duplicate key value violates unique constraint "shelves_r_mug_id_key"
DETAIL: Key (r_mug_id)=(12) already exists.

So your exclusion constraint does not do what you would expect.

You can think of your mugs positions by pulling the shelves buckets in order to make them look like they have a linear shape. Then you would have a unique key on this linear store. What Laurenz said is most probably correct.

hmm just thinking , would imagining making 64*3 long integers with all three mug ids side by side ,and using bitwise operations along with exclusion constraints might yield some results ?


Any suggestions would be appreciated.

Regards,

Rhys
Peace & Love | Live Long & Prosper

Re: exclusion constraint question

From

Achilleas Mantzios - cloud

Date:

10 March, 12:52:47

On 3/10/25 10:43, Achilleas Mantzios - cloud wrote:

On 3/8/25 21:01, Rhys A.D. Stewart wrote:
Greetings All,

I have the following table:

CREATE TABLE shelves(    shelf_id bigint PRIMARY KEY,    l_mug_id bigint UNIQUE,    c_mug_id bigint UNIQUE,    r_mug_id bigint UNIQUE,    CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id
<> r_mug_id),    EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>, r_mug_id
WITH <>) -- Not working as expected (or my expectations are wrong).
);

And some data:

INSERT INTO shelves VALUES (1,    7,    2,    1);
INSERT INTO shelves VALUES (2,    3, null, null);
INSERT INTO shelves VALUES (3, null,    1,    4);
INSERT INTO shelves VALUES (4,    4,    5, null);

Mugs on shelves, fascinating. A mug_id can only appear once in the
entire table. The check constraint handles not having the same mug_id
in each row and the unique constraints does the same for the column.
But how do I get around checking multiple columns for the same mug_id.
I'm thinking an exclusion constraint, but (a) I do not know if I am
overthinking it and (b) the exclusion constraint I have does not work
as expected, or my expectations are way off.
First i'd say look at the exclusion constraint and your unique keys. Those are defined in a mutually-auto-canceling manner. e..g
insert mugs 10, 11, and 12 in shelf 3 :
insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(3,10,11,12);
INSERT 0 1
then insert mugs 13,14,15 in shelf 4 :
insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(4,13,14,15);
ERROR: conflicting key value violates exclusion constraint "shelves_l_mug_id_c_mug_id_r_mug_id_excl"
DETAIL: Key (l_mug_id, c_mug_id, r_mug_id)=(13, 14, 15) conflicts with existing key (l_mug_id, c_mug_id, r_mug_id)=(10, 11, 12).
in order to bypass the exclusion constraint one of the mags must match a previous mug in the same position :
insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(4,13,14,12);
but then :
ERROR: duplicate key value violates unique constraint "shelves_r_mug_id_key"
DETAIL: Key (r_mug_id)=(12) already exists.
So your exclusion constraint does not do what you would expect.
You can think of your mugs positions by pulling the shelves buckets in order to make them look like they have a linear shape. Then you would have a unique key on this linear store. What Laurenz said is most probably correct.
hmm just thinking , would imagining making 64*3 long integers with all three mug ids side by side ,and using bitwise operations along with exclusion constraints might yield some results ?

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.

The idea is make sure that there is no overlap between elements in every two different rows.

Any suggestions would be appreciated.

Regards,

Rhys
Peace & Love | Live Long & Prosper

Re: exclusion constraint question

From

"Rhys A.D. Stewart"

Date:

11 March, 01:28:39

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

Re: exclusion constraint question

From

Achilleas Mantzios - cloud

Date:

11 March, 10:06:08

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

Re: exclusion constraint question

From

Greg Sabino Mullane

Date:

11 March, 17:29:05

On Tue, Mar 11, 2025 at 3:06 AM Achilleas Mantzios asked:

is it still harder than the trigger ?

I think the trigger wins: no extension needed, arguably better error output, easier to understand at a glance, and can quickly change the business logic by adjusting the function. Pretty short too. Don't know what the op tried, but here's a version I came up with. Note that this trigger allows you to remove all the UNIQUE column specifiers and the CHECK clause from the original table.

create table mugs (
mug_id bigint PRIMARY KEY,
shelf bigint -- not needed for the trigger to work, but nice to have
);

create or replace function add_a_mug() returns trigger
language plpgsql as $$
begin
if tg_op in ('UPDATE','DELETE' then
delete from mugs where mug_id in (old.l_mug_id, old.c_mug_id, old.r_mug_id);
end if;
if tg_op in ('UPDATE','INSERT') then
with mugs as (select unnest(array[new.l_mug_id,new.c_mug_id,new.r_mug_id]) as mug)
insert into mugs(mug_id, shelf) select mug, new.shelf_id
from mugs where mug is not null;
end if;
return null;
end
$$;

create trigger take_one_down_pass_it_around after insert or update or delete
on shelves for each row execute function add_a_mug();

Cheers,

Greg

Crunchy Data - https://www.crunchydata.com

Enterprise Postgres Software Products & Tech Support