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 1vT6PI-00CrTX-2f for pgsql-general@arkaria.postgresql.org; Tue, 09 Dec 2025 22:42:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vT6PH-007vwa-1P for pgsql-general@arkaria.postgresql.org; Tue, 09 Dec 2025 22:42:07 +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 1vT6M4-007qlx-0v for pgsql-general@lists.postgresql.org; Tue, 09 Dec 2025 22:38:48 +0000 Received: from mail-pf1-x42f.google.com ([2607:f8b0:4864:20::42f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vT6M2-0047tZ-0C for pgsql-general@lists.postgresql.org; Tue, 09 Dec 2025 22:38:48 +0000 Received: by mail-pf1-x42f.google.com with SMTP id d2e1a72fcca58-7bb710d1d1dso9723973b3a.1 for ; Tue, 09 Dec 2025 14:38:45 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1765319923; x=1765924723; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=UQbUHWUAmWwdCVoeeaUHl+j7YHrc+ni6SY7O8ZkEMeU=; b=e5eBj8VFh6UTaQyFy02yHx5hipDhWsalwfx7CEmM9a1b5WUiZZwvZC20AeHqJiHWF0 1U+xaiCVkfkDxwHZse+AJZj7rM8ammBzdXp3TCJC+eVpMVcGQ2mFomWilujVzK8qDA9D MkyiaDUqu6fFpHh/JdPS3869qDcCX+ahGI2nmvLdQugmEdbDsHe8q+6pTKmSjdQB0x2Y uBKSua1xr+spHduo9Be20OtxtLWd0Oh8zdZ+7+DAhza/eV0NyB2O6PUPu5kVjQOZ90Py qFoWr8DjV1RZiRF+PN0To0xFZVE5SCjNC299RT1dBdb+iKN+McSy2xGGU/Bn6WEoprF2 R4tA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1765319923; x=1765924723; h=cc: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=UQbUHWUAmWwdCVoeeaUHl+j7YHrc+ni6SY7O8ZkEMeU=; b=ms85P551Ro/19M+wW/47LVbBLMVrmgkx8QR22+QQkCnU7IG1YVM+t4LFcUhvNY5NCa WOKv5KPsbUXNZrsx5YvVDeirb5NDvFPXzY9/oUY4uVlaO1c/vZeOsnpfFAV1PalnJ07G nS/3k3+rdLjUoAeg/emzMAIU0isTy5chHJwiK+gHmnv9F8M61xcOijrESqpmMqmFq2gn y6Z+1Ktt2YwYRkOuvDqfNYeaalJZKUJV78vKrhzw6xdRo9JfrUqIwEBOArbl9+552qwx oR3tqtfFELbqcrb9sejVO3ZiRK1EHTOIgYvc0MNeLv5LZFQufQkawrU+o/xnw+UxPdED 0LBw== X-Gm-Message-State: AOJu0Yw/VLpCOZZ551Kn3sjHNk8jLAq2qCaBmN+p5dBnsqXkE4Lgxp7N ntChkoQBjbOjcCyY1ezcFUUzoWyfNbbc1EdjNpWU3UB75tXikeVeDsZXoTHxLGyLqsRDnRtL5mM vDtPkfKqbDRmELrxoc3TouVFqXXA5x8VSkQ== X-Gm-Gg: ASbGncvg1RlS/dhHwmMaUtGRmof20LUz+WiworUqOJjXtmWvqrbyT2aKptxmRkOIRAo AXN8G6JPYysW33hgSnctLKgK+aeSol4iU8AzQwt8PP79xlnFAmejfJ2Efxm+unvQD3ez+VUsc1u 46UMmANoP8g2/u/FuG+ZUvZ+B/qmi+G620uIx05h1rVO+EGVuyzsEcLu1llHZ+DgGfLrGKNsv2P cHuoPg148+jeeasK4CM9VbWozz/xdwWLiQoxsQ0hiH+OuTyD6V7zRoDhPibL1h3phdUTIY3GOMG P3+QBRzCAj+GgJnkJcMQ+iSymmAB1Q== X-Google-Smtp-Source: AGHT+IG+qWEQCUR9ZAmXj1hKbnDI7CTe5Ya+ewVkCiw19GiWuQCsiDQW9dBcLOHj4k7O9doHuxtWRdIUIYcb2lFAMGw= X-Received: by 2002:a05:6a00:228a:b0:7ee:f5f6:68f6 with SMTP id d2e1a72fcca58-7f22e48a47amr258079b3a.38.1765319923163; Tue, 09 Dec 2025 14:38:43 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Justin Swanhart Date: Tue, 9 Dec 2025 17:38:32 -0500 X-Gm-Features: AQt7F2rHIsrFv-XHxa8qZct6DF4GBoL6dtOz3hFCRDbIilq7OWNr6oDKWiOLVS4 Message-ID: Subject: Re: How do I check for NULL To: Thiemo Kellner Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000ef674c06458c92be" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ef674c06458c92be Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable The key is the scalar subquery. A scalar subquery which selects no rows returns NULL. https://sqlfiddle.com/postgresql/online-compiler?id=3De439059a-d46d-4d49-b8= ab-9ff533656066 On Tue, Dec 9, 2025, 5:33=E2=80=AFPM Thiemo Kellner wrote: > > On 12/9/25 18:29, David G. Johnston wrote: > > On Tue, Dec 9, 2025 at 10:14=E2=80=AFAM Thiemo Kellner > > wrote: > > > > I feel, you meant to say, the subquery does not return any record > > which is not the same as returns NULL. > > > > > > For a scalar subquery the final output of a zero-row query is the null > > value. > > > To me, it does not look like that (please note the empty line in the > last example). Can you point me to the documentation saying that 0 rows > is sometimes equal to 1 row? > > postgres=3D# select * from pg_user; > usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls > | passwd | valuntil | useconfig > ----------+----------+-------------+----------+---------+--------------+-= ---------+----------+----------- > > > postgres | 10 | t | t | t | t | > ******** | | > (1 row) > > postgres=3D# select usename from pg_user where false; > usename > --------- > (0 rows) > > postgres=3D# select null as usename from pg_user; > usename > --------- > > (1 row) > > > --000000000000ef674c06458c92be Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
The key is the scalar subquery.=C2=A0 A scalar subquery w= hich selects no rows returns NULL.


On Tue, Dec 9, 20= 25, 5:33=E2=80=AFPM Thiemo Kellner <thiemo@gelassene-pferde.biz= > wrote:

On 12/9/25 18:29, David G. Johnston wrote:
> On Tue, Dec 9, 2025 at 10:14=E2=80=AFAM Thiemo Kellner
> <thiemo@gelassene-pferde.biz> wrote:
>
>=C2=A0 =C2=A0 =C2=A0I feel, you meant to say, the subquery does not ret= urn any record
>=C2=A0 =C2=A0 =C2=A0which is not the same as returns NULL.
>
>
> For a scalar subquery the final output of a zero-row query is the null=
> value.
>
To me, it does not look like that (please note the empty line in the
last example). Can you point me to the documentation saying that 0 rows is sometimes equal to 1 row?

postgres=3D# select * from pg_user;
=C2=A0=C2=A0usename=C2=A0 | usesysid | usecreatedb | usesuper | userepl | u= sebypassrls
|=C2=A0 passwd=C2=A0 | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+---= -------+----------+-----------

=C2=A0=C2=A0postgres |=C2=A0 =C2=A0 =C2=A0 =C2=A010 | t=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0| t=C2=A0 =C2=A0 =C2=A0 =C2=A0 | t=C2=A0 =C2=A0 =C2=A0= =C2=A0| t =C2=A0 |
******** |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
(1 row)

postgres=3D# select usename from pg_user where false;
=C2=A0=C2=A0usename
---------
(0 rows)

postgres=3D# select null as usename from pg_user;
=C2=A0=C2=A0usename
---------

(1 row)


--000000000000ef674c06458c92be--