Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: exclusion constraint question
From: personal@emanuelseemann.ch
In-Reply-To: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
Date: Sat, 8 Mar 2025 20:26:44 +0100
Cc: pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <9D35A370-D623-4858-93CA-BBEE0F27AADF@emanuelseemann.ch>
References: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
To: "Rhys A.D. Stewart" <rhys.stewart@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/9D35A370-D623-4858-93CA-BBEE0F27AADF%40emanuelseemann.ch>
Precedence: bulk

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 8 Mar 2025, at 20:01, Rhys A.D. Stewart <rhys.stewart@gmail.com> =
wrote:
>=20
> Greetings All,
>=20
> I have the following table:
>=20
> 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).
> );
>=20
> And some data:
>=20
> 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);
>=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.
>=20
> Regards,
>=20
> Rhys
> Peace & Love | Live Long & Prosper
>=20
>=20