Re: exclusion constraint question

On Sat, Mar 8, 2025 at 12:01 PM Rhys A.D. Stewart <rhys(dot)stewart(at)gmail(dot)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.