Message-ID: <56afabfed80a7da28bec5a9cf12cce853fc02c3f.camel@cybertec.at>
Subject: Re: exclusion constraint question
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Rhys A.D. Stewart" <rhys.stewart@gmail.com>, 
	pgsql-general@lists.postgresql.org
Date: Sat, 08 Mar 2025 21:26:09 +0100
In-Reply-To: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
References: 
	<CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/56afabfed80a7da28bec5a9cf12cce853fc02c3f.camel%40cybertec.at>
Precedence: bulk

On Sat, 2025-03-08 at 14:01 -0500, Rhys A.D. Stewart wrote:
> I have the following table:
>=20
> CREATE TABLE shelves(
> =C2=A0=C2=A0=C2=A0 shelf_id bigint PRIMARY KEY,
> =C2=A0=C2=A0=C2=A0 l_mug_id bigint UNIQUE,
> =C2=A0=C2=A0=C2=A0 c_mug_id bigint UNIQUE,
> =C2=A0=C2=A0=C2=A0 r_mug_id bigint UNIQUE,
> =C2=A0=C2=A0=C2=A0 CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id A=
ND c_mug_id
> <> r_mug_id),
> =C2=A0=C2=A0=C2=A0 EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>=
, r_mug_id
> WITH <>) -- Not working as expected (or my expectations are wrong).
> );
>=20
> And some data:
>=20
> INSERT INTO shelves VALUES (1,=C2=A0=C2=A0=C2=A0 7,=C2=A0=C2=A0=C2=A0 2,=
=C2=A0=C2=A0=C2=A0 1);
> INSERT INTO shelves VALUES (2,=C2=A0=C2=A0=C2=A0 3, null, null);
> INSERT INTO shelves VALUES (3, null,=C2=A0=C2=A0=C2=A0 1,=C2=A0=C2=A0=C2=
=A0 4);
> INSERT INTO shelves VALUES (4,=C2=A0=C2=A0=C2=A0 4,=C2=A0=C2=A0=C2=A0 5, =
null);
>=20
> 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.
>=20
> 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