Subject: How do I check for NULL

From: Igor Korot <[email-masked]>
Date: Mon, 8 Dec 2025 18:39:46 -0800

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) ==
NULL, "postgres", <select_result>), /*more data follow*/);

What would be the best way to achieve this?

Thank you..

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 8 Dec 2025 19:44:01 -0700

> What would be the best way to achieve this?

The “coalesce” function.

David J.

---

From: Ron Johnson <[email-masked]>
Date: Mon, 8 Dec 2025 21:51:55 -0500

> 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".

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

---

From: Ron Johnson <[email-masked]>
Date: Mon, 8 Dec 2025 21:58:52 -0500

On Mon, Dec 8, 2025 at 9:51 PM Ron Johnson <[email-masked]> wrote:

> distinct (not equal). *The default* is that they are *distinct*, so that
> a *unique index could contain multiple null values in a column*."
>

It does work:

dba=# create table foo (a int, b int, c int, d int);
CREATE TABLE
dba=# create unique index i_foo_u1 on foo (a, b, d);
CREATE INDEX
dba=#
dba=# insert into foo values (1, 1, 1, 1);
INSERT 0 1
dba=# insert into foo values (2, 2, 2, null);
INSERT 0 1
dba=# insert into foo values (3, 3, 3, null);
INSERT 0 1
dba=# insert into foo values (4, 4, 4, null);
INSERT 0 1

---

From: Igor Korot <[email-masked]>
Date: Mon, 8 Dec 2025 23:53:08 -0800

Hi, Davd,

>> What would be the best way to achieve this?

This is the query I use for my ODBC calls:

            qry2 = L"INSERT INTO \"test\" VALUES( ?, ?, (SELECT c.oid
FROM pg_class c, pg_namespace nc WHERE nc.oid = c.relnamespace AND
c.relname = ? AND nc.nspname = ?), COALESCE((SELECT tableowner FROM
pg_tables WHERE tablename = ? AND schemaname = ?), \"postgres\"), ...)
ON CONFLICT DO NOTHING;";

Calling SQLExecute after parameter binding results in

"L"ERROR: column \"postgres\" does not exist;\nError while preparing
parameters"std::basic_string<wchar_t,std::char_traits<wchar_t>,std::allocator<wchar_t>

Thank you.

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 9 Dec 2025 06:43:57 -0700

> wchar_t>,std::allocator<wchar_t>
Use single quotes for a string literal value.

David J.

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 9 Dec 2025 08:09:51 -0800

> parameters"std::basic_string<wchar_t,std::char_traits<wchar_t>,std::allocator<wchar_t>

Read:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

-- 
Adrian Klaver
[email-masked]

---

From: Thiemo Kellner <[email-masked]>
Date: Tue, 9 Dec 2025 18:14:27 +0100 (GMT+01:00)

Hi

I believe there is a misconception. I feel, you meant to say, the subquery does not return any record which is not the same as returns NULL.

In any case, I suggest you to use the "insert select" construct, see examples in https://www.postgresql.org/docs/current/sql-insert.html, e.g. "NSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';"

Cheers

Thiemo

---

From: Thiemo Kellner <[email-masked]>
Date: Tue, 9 Dec 2025 18:16:28 +0100 (GMT+01:00)

Btw, the exact error message could be helpful and should be provided to see misinterpretations.

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 9 Dec 2025 10:29:17 -0700

On Tue, Dec 9, 2025 at 10:14 AM Thiemo Kellner <[email-masked]>
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.

David J.

---

From: Juan Rodrigo Alejandro Burgos Mella <[email-masked]>
Date: Tue, 9 Dec 2025 15:40:04 -0500

Hi
Did you try setting a default value to the field?

Atte
JRBM

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 9 Dec 2025 14:56:24 -0700

> Did you try setting a default value to the field?
Defaults don't work if you actually intend to conditionally override them
-- or at least it requires something beyond a simple self-contained SQL
command to accomplish.

David J.

---

From: Thiemo Kellner <[email-masked]>
Date: Tue, 9 Dec 2025 23:33:13 +0100

On 12/9/25 18:29, David G. Johnston wrote:
> On Tue, Dec 9, 2025 at 10:14 AM Thiemo Kellner 
> <[email-masked]> wrote:
>
>     I feel, you meant to say, the subquery does not return any record
>     which is not the same as returns NULL.
>
>
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=# select * from pg_user;
  usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls 
|  passwd  | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+----------- 

  postgres |       10 | t           | t        | t       | t   | 
******** |          |
(1 row)

postgres=# select usename from pg_user where false;
  usename
---------
(0 rows)

postgres=# select null as usename from pg_user;
  usename
---------

(1 row)

Subject: Re: Operational issues when max_replication_slots is exhausted
From: Laurenz Albe <[email-masked]>
To: Ahmed Et-tanany <[email-masked]>, 
	[email-masked]
Date: Mon, 15 Dec 2025 16:17:52 +0100
Archived-At: <https://www.postgresql.org/message-id/bfd667fac146fd2ff451b6c0b0750d7f979cf6b0.camel%40cybertec.at>

On Mon, 2025-12-15 at 12:58 +0100, Ahmed Et-tanany wrote:
> Our problem is that when our customers use up all available replication s=
lots for logical replication,
> our database management tasks that also require a slot fail (for example,=
 creating the required
> replication slot for a new physical standby). Since increasing `max_repli=
cation_slots` requires
> a restart, we would like to avoid that if possible.
>=20
> One idea we have considered is patching PostgreSQL to add a new GUC param=
eter that would allow
> a superuser to reserve a certain number of replication slots usable only =
for management tasks.
>=20
> Is this a known issue that might be addressed in PostgreSQL at some point=
? If not,
> what would be a good way to solve this problem?

It is conceivable that somebody might change the behavior at some point (co=
mpare
"reserved_connections").  If you write or sponsor a patch, that would incre=
ase
the likelihood.

Right now, my only suggestion is to set "max_replication_slots" high.

Yours,
Laurenz Albe

---

From: Justin Swanhart <[email-masked]>
Date: Tue, 9 Dec 2025 17:38:32 -0500

The key is the scalar subquery.  A scalar subquery which selects no rows
returns NULL.

https://sqlfiddle.com/postgresql/online-compiler?id=e439059a-d46d-4d49-b8ab-9ff533656066