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@xxxxxxxxx> 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 > >
