public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Rhys A.D. Stewart <[email protected]>
To: [email protected]
Subject: Re: exclusion constraint question
Date: Sat, 08 Mar 2025 21:26:09 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
References: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: exclusion constraint question
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox