MIME-Version: 1.0
References: <CA+FnnTw=18Rba2XgceRUFKQo5P7Xf9QhXT6wKF=DgzYT6K9-sw@mail.gmail.com>
In-Reply-To: <CA+FnnTw=18Rba2XgceRUFKQo5P7Xf9QhXT6wKF=DgzYT6K9-sw@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 8 Dec 2025 21:51:55 -0500
Message-ID: <CANzqJaAx6RYPzEpgHhioHm0xCQ=OhZjbs7aNHJD-Dex3LyfcpA@mail.gmail.com>
Subject: Re: How do I check for NULL
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000004d78e706457bff5c"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAx6RYPzEpgHhioHm0xCQ%3DOhZjbs7aNHJD-Dex3LyfcpA%40mail.gmail.com>
Precedence: bulk

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

On Mon, Dec 8, 2025 at 9:40=E2=80=AFPM Igor Korot <ikorot01@gmail.com> wrot=
e:

> Hi, ALL,
> Consider the following scenario:
>
> CREATE TABLE test(a INT, b VARCHAR(256), c INT, d VARCHAR(256), /*
> more fields follows*/);
> CREATE UNIQUE INDEX test_x( b, c, d );
>
> Now I try to do:
>
> INSERT INTO test VALUES( 0, 'abc', 12345, (SELECT foo FROM bar),
> /*more data follow*/);
>
> My problem is:
>
> The SELECT can either return data or NULL.
> Everything is good when the data is returned, but the insert fails
> when the NULL is returned, because the field "d" is a part of UNIQUE
> INDEX.
>
> However,, I'd like to still insert the record and I'd like to do somethin=
g
> like:
>
> INSERT INTO test VALUES( 0, 'abc', 12345, IF( (SELECT foo FROM bar) =3D=
=3D
> NULL, "postgres", <select_result>), /*more data follow*/);
>
> What would be the best way to achieve this?
>

https://www.postgresql.org/docs/15/sql-createindex.html section on NULLS
DISTINCT says
"Specifies whether for a unique index, null values should be considered
distinct (not equal). *The default* is that they are *distinct*, so
that a *unique
index could contain multiple null values in a column*."

That seems to mean multiple rows can have NULL in column "d".

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Dec 8, 2025 at 9:40=E2=80=AFPM Ig=
or Korot &lt;<a href=3D"mailto:ikorot01@gmail.com">ikorot01@gmail.com</a>&g=
t; wrote:</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 so=
lid rgb(204,204,204);padding-left:1ex">Hi, ALL,<br>
Consider the following scenario:<br>
<br>
CREATE TABLE test(a INT, b VARCHAR(256), c INT, d VARCHAR(256), /*<br>
more fields follows*/);<br>
CREATE UNIQUE INDEX test_x( b, c, d );<br>
<br>
Now I try to do:<br>
<br>
INSERT INTO test VALUES( 0, &#39;abc&#39;, 12345, (SELECT foo FROM bar),<br=
>
/*more data follow*/);<br>
<br>
My problem is:<br>
<br>
The SELECT can either return data or NULL.<br>
Everything is good when the data is returned, but the insert fails<br>
when the NULL is returned, because the field &quot;d&quot; is a part of UNI=
QUE<br>
INDEX.<br>
<br>
However,, I&#39;d like to still insert the record and I&#39;d like to do so=
mething like:<br>
<br>
INSERT INTO test VALUES( 0, &#39;abc&#39;, 12345, IF( (SELECT foo FROM bar)=
 =3D=3D<br>
NULL, &quot;postgres&quot;, &lt;select_result&gt;), /*more data follow*/);<=
br>
<br>
What would be the best way to achieve this?<br></blockquote><div><br></div>=
<div><a href=3D"https://www.postgresql.org/docs/15/sql-createindex.html">ht=
tps://www.postgresql.org/docs/15/sql-createindex.html</a> section on=C2=A0<=
span style=3D"background-color:rgb(248,249,250);color:rgb(0,0,0);font-famil=
y:monospace,monospace;font-size:14.4px">NULLS DISTINCT</span>=C2=A0says=C2=
=A0</div><div><span style=3D"color:rgb(0,0,0);font-family:&quot;Open Sans&q=
uot;,sans-serif;font-size:14.4px">&quot;Specifies whether for a unique inde=
x, null values should be considered distinct (not equal). <b>The default</b=
> is that they are <b>distinct</b>, so that a <b>unique index could contain=
 multiple null values in a column</b>.&quot;</span></div><div><span style=
=3D"color:rgb(0,0,0);font-family:&quot;Open Sans&quot;,sans-serif;font-size=
:14.4px"><br></span></div><div><font color=3D"#000000" face=3D"Open Sans, s=
ans-serif"><span style=3D"font-size:14.4px">That seems to mean multiple row=
s can have NULL in column &quot;d&quot;.</span></font></div><div><br></div>=
</div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr"=
 class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and =
butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;=
Redacted&gt; lobster!</div></div></div></div></div></div>

--0000000000004d78e706457bff5c--