Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vSnwV-0063Xr-2g for pgsql-general@arkaria.postgresql.org; Tue, 09 Dec 2025 02:59:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vSnwU-002TpF-1V for pgsql-general@arkaria.postgresql.org; Tue, 09 Dec 2025 02:59:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vSnwU-002Tp7-0I for pgsql-general@lists.postgresql.org; Tue, 09 Dec 2025 02:59:10 +0000 Received: from mail-oi1-x22e.google.com ([2607:f8b0:4864:20::22e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vSnwR-003xjb-2G for pgsql-general@postgresql.org; Tue, 09 Dec 2025 02:59:10 +0000 Received: by mail-oi1-x22e.google.com with SMTP id 5614622812f47-450c576a335so1964824b6e.1 for ; Mon, 08 Dec 2025 18:59:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1765249145; x=1765853945; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=poxkSFhIeWSYJlTw6LAYccmxJQmfTyeCWYqIzHePbXA=; b=f4LwoLl5h/ol5GdVnOTGiERuM0DRJKXVx3zH4yEkcWKhHMwX06ib5jb/UvOfmYeDLU mfB/MWXK8YXPYrlgc97uMaZpoEpDUHitt4bNUVxh16pJaiKvyw9SEZFpCRSl44+AO5za ondGbaDH5B1q9Yak9c3ynY3Bcb/a/BuIw9n1g7WKQ45TFiWeuhlP5xFK25niRtcotbob NBKd1h9XC9qHZnh97g/AYXysXeo3B6NSbbBS58riNAsvXZMihk29br6lXp7xjlj7Bg2m QeV/fncQTzb+jR9je4s7vwKlCNbdfZeXYFaVhTU75nGj2S63hXD5I4jWj0y9XmnCkxHe u6ng== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1765249145; x=1765853945; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=poxkSFhIeWSYJlTw6LAYccmxJQmfTyeCWYqIzHePbXA=; b=DwGczFGi2gPTb4Yz+UbL21aEQ+mJmaYCpMqChokZJbZRnXeFGhb+3biYBnmVzBqeqB DCaKC6hCZf4pYAVVcYEoVIAP3BpPWciwdUDz46bDgktQncgEAGDhpEh02+/omeX4fm/2 6xjPjQq0ZLbE9yzX4f1DjhWOW0k00tF24D5foxHlgQiRjecQOysCsEmcTrA/8Vm7EF/6 ng0p8+rmoVq2bIIcxM5BTpq/n+/N5R+t/IvUagYM0WQaMOWo1epXCzmzKd4MeR/U9UgM u3VX50tnvK+d89vxhkIkqoBYbVKoNCHPdK/fOPoGs5mZ5M6F+K9/Br3Tay+5/ByEf2sA fpkQ== X-Gm-Message-State: AOJu0Ywq0Rwp5NWKruDoNn9WuiFQ3lAMf2XYL1+h6wqn7ttgD0oXINT3 oxqwulQZdvm0FBwxKIVZcnt5JONA+omoHloS8IncobQcURHHwELR7Na/nXrWQRiIPH2jagsbqhL 1f2Xxmt/G2TwoS7XJAwwHAHsL6reJXSo= X-Gm-Gg: ASbGncvxXZJh+A2+UuZQzDAOsJQgci9wxIvRvdjZCcvy4puHmPAcRpslgGNlabPWDgS t8NmbeXZuE/M2lZbYf4+bTUx3As2C7tD5YKgiU96VJNoD2dhxgk0N1OBqMBRPGZjhrQiNv91r1r Ku0kMI1LEh37jpzfB8h84OxugO5gS4U2zfLlSDHVLb2v/1dJXR8Cun+lM/F/H04EsAYJ1/4rXel u9QqoUgDBYA2iGVD0B/QLJcpVnaGQ== X-Google-Smtp-Source: AGHT+IGFXiOYtEba2nOYD08LXZPAC3bxThc35liU/mPCyzK7+bS/+hhuB6xTwTRQMkN0gfbRDor4VOGXtw4p88+VdUE= X-Received: by 2002:a05:6808:c18a:b0:450:3c6f:24fc with SMTP id 5614622812f47-4539e0ccf04mr3768195b6e.31.1765249143411; Mon, 08 Dec 2025 18:59:03 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Mon, 8 Dec 2025 21:58:52 -0500 X-Gm-Features: AQt7F2q5mppCl2nzLsTwTE4P3GEeaQ8BYmjr83h0ptOY_pk_EyvywkdyOXuoggA Message-ID: Subject: Re: How do I check for NULL To: pgsql-general Content-Type: multipart/alternative; boundary="00000000000022171d06457c1812" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000022171d06457c1812 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Dec 8, 2025 at 9:51=E2=80=AFPM Ron Johnson wrote: > On Mon, Dec 8, 2025 at 9:40=E2=80=AFPM Igor Korot wr= ote: > >> 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 >> something like: >> >> INSERT INTO test VALUES( 0, 'abc', 12345, IF( (SELECT foo FROM bar) =3D= =3D >> NULL, "postgres", ), /*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". > It does work: dba=3D# create table foo (a int, b int, c int, d int); CREATE TABLE dba=3D# create unique index i_foo_u1 on foo (a, b, d); CREATE INDEX dba=3D# dba=3D# insert into foo values (1, 1, 1, 1); INSERT 0 1 dba=3D# insert into foo values (2, 2, 2, null); INSERT 0 1 dba=3D# insert into foo values (3, 3, 3, null); INSERT 0 1 dba=3D# insert into foo values (4, 4, 4, null); INSERT 0 1 --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000022171d06457c1812 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Dec 8, 2025 at 9:51=E2=80=AFPM Ro= n Johnson <ronljohnsonjr@gmai= l.com> wrote:
=
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 UNI= QUE
INDEX.

However,, I'd like to still insert the record and I'd like to do so= mething like:

INSERT INTO test VALUES( 0, 'abc', 12345, IF( (SELECT foo FROM bar)= =3D=3D
NULL, "postgres", <select_result>), /*more data follow*/);<= br>
What would be the best way to achieve this?

=
https://www.postgresql.org/docs/15/sql-createindex.html= section on=C2=A0NULLS DISTINCT=C2=A0says=C2=A0
"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 in= dex could contain multiple null values in a column."
<= div>
That seems to m= ean multiple rows can have NULL in column "d".

It does work:

<= /div>
dba=3D# create table foo (a int, b int, = c int, d int);
CREATE TABLE
dba=3D# create unique index i_foo_u1 on f= oo (a, b, d);
CREATE INDEX
dba=3D#
dba=3D# insert into foo values= (1, 1, 1, 1);
INSERT 0 1
dba=3D# insert into foo values (2, 2, 2, nu= ll);
INSERT 0 1
dba=3D# insert into foo values (3, 3, 3, null);
IN= SERT 0 1
dba=3D# insert into foo values (4, 4, 4, null);
INSERT 0 1

-- <= /span>
Death= to <Redacted>, and butter sauce.
Don't boil me, I'm stil= l alive.
<Redacted> lobster!
--00000000000022171d06457c1812--