MIME-Version: 1.0
References: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
In-Reply-To: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sat, 8 Mar 2025 12:34:28 -0700
Message-ID: <CAKFQuwYDTg1pqCUppYrUc_Sm-wAANVXC2YqgUxJLu2NBAnNmQw@mail.gmail.com>
Subject: Re: exclusion constraint question
To: "Rhys A.D. Stewart" <rhys.stewart@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000fd8e05062fd9d576"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYDTg1pqCUppYrUc_Sm-wAANVXC2YqgUxJLu2NBAnNmQw%40mail.gmail.com>
Precedence: bulk

--000000000000fd8e05062fd9d576
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Mar 8, 2025 at 12:01=E2=80=AFPM Rhys A.D. Stewart <rhys.stewart@gma=
il.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.

--000000000000fd8e05062fd9d576
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Sat, Mar 8, 2025 at 12:01=E2=80=AFPM Rhys A.D. Stewart =
&lt;<a href=3D"mailto:rhys.stewart@gmail.com">rhys.stewart@gmail.com</a>&gt=
; wrote:</span></div></div><div class=3D"gmail_quote gmail_quote_container"=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left:1px solid rgb(204,204,204);padding-left:1ex"><br>
=C2=A0 =C2=A0 CHECK (l_mug_id &lt;&gt; c_mug_id AND l_mug_id &lt;&gt; r_mug=
_id AND c_mug_id<br>
&lt;&gt; r_mug_id),<br>
=C2=A0 =C2=A0 EXCLUDE USING gist (l_mug_id WITH &lt;&gt;, c_mug_id WITH &lt=
;&gt;, r_mug_id<br>
WITH &lt;&gt;) -- Not working as expected (or my expectations are wrong).<b=
r>
);<br>
<br>
And some data:<br>
<br>
INSERT INTO shelves VALUES (1,=C2=A0 =C2=A0 7,=C2=A0 =C2=A0 2,=C2=A0 =C2=A0=
 1);<br>
INSERT INTO shelves VALUES (2,=C2=A0 =C2=A0 3, null, null);<br>
INSERT INTO shelves VALUES (3, null,=C2=A0 =C2=A0 1,=C2=A0 =C2=A0 4);<br>
INSERT INTO shelves VALUES (4,=C2=A0 =C2=A0 4,=C2=A0 =C2=A0 5, null);<br>
<br>
Any suggestions would be appreciated.<br><br></blockquote><div><br></div><d=
iv class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"=
>Using &quot;equals&quot; or &quot;not equals&quot; when one of the inputs =
can be null is not usually what you want to do.=C2=A0 The comparison evalua=
tes to NULL which the constraint allows.</div><div class=3D"gmail_default" =
style=3D"font-family:arial,helvetica,sans-serif"><br></div><div class=3D"gm=
ail_default" style=3D"font-family:arial,helvetica,sans-serif">David J.</div=
><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-ser=
if"><br></div></div></div>

--000000000000fd8e05062fd9d576--