MIME-Version: 1.0
In-Reply-To: <CAE8z92GN0-5J=4q6RSsPGk_OP-Y_EHVNgQimTWwcq+BE+HGqRQ@mail.gmail.com>
References: <CAE8z92GN0-5J=4q6RSsPGk_OP-Y_EHVNgQimTWwcq+BE+HGqRQ@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Wed, 18 Dec 2024 21:14:43 -0700
Message-ID: <CAKFQuwa2jcBnsZxHmNn7_1MjcmOZx_fU93epBx9+orw3yMNgKw@mail.gmail.com>
Subject: Re: Wrapping a where clause to preserve rows with nulls
To: Adrian Garcia Badaracco <adrian@adriangb.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001f1df9062997c5c0"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwa2jcBnsZxHmNn7_1MjcmOZx_fU93epBx9%2Borw3yMNgKw%40mail.gmail.com>
Precedence: bulk

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

On Wednesday, December 18, 2024, Adrian Garcia Badaracco <
adrian@adriangb.com> wrote:

>
> Is there any way to include the rows where the predicate evaluates to nul=
l
> while still using an index?
>

That seems quite unlikely.  Your definition of equality is incompatible
with the system=E2=80=99s standard definition while requiring that the stan=
dard
equals operator be used in the query.  Null values and non-null values are
not considered equal, or, put another way, always considered distinct from
each other.  A btree index, which handles =3D, can=E2=80=99t be told to beh=
ave
differently and so cannot fulfill your desire to produce rows where the
stored value is null; it can only produce those equal to 5000.

David J.

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

On Wednesday, December 18, 2024, Adrian Garcia Badaracco &lt;<a href=3D"mai=
lto:adrian@adriangb.com">adrian@adriangb.com</a>&gt; wrote:<br><blockquote =
class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid=
;padding-left:1ex"><div dir=3D"ltr"><div><br></div><div>Is there any way to=
 include the rows where the predicate evaluates to null while still using a=
n index?</div></div></blockquote><div><br></div><div>That seems quite unlik=
ely.=C2=A0 Your definition of equality is incompatible with the system=E2=
=80=99s standard definition while requiring that the standard equals operat=
or be used in the query.=C2=A0 Null values and non-null values are not cons=
idered equal, or, put another way, always considered distinct from each oth=
er.=C2=A0 A btree index, which handles =3D, can=E2=80=99t be told to behave=
 differently and so cannot fulfill your desire to produce rows where the st=
ored value is null; it can only produce those equal to 5000.</div><div><br>=
</div><div>David J.</div>

--0000000000001f1df9062997c5c0--