exclusion constraint question

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 columns instead of names)

Does this work for you?

Best,
Emanuel

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.

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

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 ?

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.

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

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 ?

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