public inbox for [email protected]
help / color / mirror / Atom feedHow do I check for NULL
14+ messages / 7 participants
[nested] [flat]
* How do I check for NULL
@ 2025-12-09 02:39 Igor Korot <[email protected]>
0 siblings, 5 replies; 14+ messages in thread
From: Igor Korot @ 2025-12-09 02:39 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
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..
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How do I check for NULL
@ 2025-12-09 02:44 David G. Johnston <[email protected]>
parent: Igor Korot <[email protected]>
4 siblings, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2025-12-09 02:44 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Monday, December 8, 2025, Igor Korot <[email protected]> wrote:
>
>
> 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?
>
The “coalesce” function.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How do I check for NULL
@ 2025-12-09 02:51 Ron Johnson <[email protected]>
parent: Igor Korot <[email protected]>
4 siblings, 1 reply; 14+ messages in thread
From: Ron Johnson @ 2025-12-09 02:51 UTC (permalink / raw)
To: pgsql-general
On Mon, Dec 8, 2025 at 9:40 PM Igor Korot <[email protected]> wrote:
> 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?
>
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!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How do I check for NULL
@ 2025-12-09 02:58 Ron Johnson <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Ron Johnson @ 2025-12-09 02:58 UTC (permalink / raw)
To: pgsql-general
On Mon, Dec 8, 2025 at 9:51 PM Ron Johnson <[email protected]> wrote:
> On Mon, Dec 8, 2025 at 9:40 PM Igor Korot <[email protected]> wrote:
>
>> 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?
>>
>
> 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=# 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
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How do I check for NULL
@ 2025-12-09 07:53 Igor Korot <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 2 replies; 14+ messages in thread
From: Igor Korot @ 2025-12-09 07:53 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Hi, Davd,
On Mon, Dec 8, 2025 at 6:44 PM David G. Johnston
<[email protected]> wrote:
>
> On Monday, December 8, 2025, Igor Korot <[email protected]> wrote:
>>
>>
>> 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?
>
>
> The “coalesce” function.
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.
>
> David J.
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How do I check for NULL
@ 2025-12-09 13:43 David G. Johnston <[email protected]>
parent: Igor Korot <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2025-12-09 13:43 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Tuesday, December 9, 2025, Igor Korot <[email protected]> wrote:
> Hi, Davd,
>
> On Mon, Dec 8, 2025 at 6:44 PM David G. Johnston
> <[email protected]> wrote:
> >
> > On Monday, December 8, 2025, Igor Korot <[email protected]> wrote:
> >>
> >>
> >> 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?
> >
> >
> > The “coalesce” function.
>
> 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>
>
>
Use single quotes for a string literal value.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How do I check for NULL
@ 2025-12-09 16:09 Adrian Klaver <[email protected]>
parent: Igor Korot <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: Adrian Klaver @ 2025-12-09 16:09 UTC (permalink / raw)
To: Igor Korot <[email protected]>; David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On 12/8/25 23:53, Igor Korot wrote:
> Hi, Davd,
>
> On Mon, Dec 8, 2025 at 6:44 PM David G. Johnston
> <[email protected]> wrote:
>>
>> On Monday, December 8, 2025, Igor Korot <[email protected]> wrote:
>>>
>>>
>>> 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?
>>
>>
>> The “coalesce” function.
>
> 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>
>>
Read:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
>
> Thank you.
>>
>> David J.
>>
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 14+ messages in thread
* How do I check for NULL
@ 2025-12-09 17:14 Thiemo Kellner <[email protected]>
parent: Igor Korot <[email protected]>
4 siblings, 1 reply; 14+ messages in thread
From: Thiemo Kellner @ 2025-12-09 17:14 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* How do I check for NULL
@ 2025-12-09 17:16 Thiemo Kellner <[email protected]>
parent: Igor Korot <[email protected]>
4 siblings, 0 replies; 14+ messages in thread
From: Thiemo Kellner @ 2025-12-09 17:16 UTC (permalink / raw)
To: [email protected]
Btw, the exact error message could be helpful and should be provided to see misinterpretations.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How do I check for NULL
@ 2025-12-09 17:29 David G. Johnston <[email protected]>
parent: Thiemo Kellner <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2025-12-09 17:29 UTC (permalink / raw)
To: Thiemo Kellner <[email protected]>; +Cc: [email protected]
On Tue, Dec 9, 2025 at 10:14 AM Thiemo Kellner <[email protected]>
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How do I check for NULL
@ 2025-12-09 20:40 Juan Rodrigo Alejandro Burgos Mella <[email protected]>
parent: Igor Korot <[email protected]>
4 siblings, 1 reply; 14+ messages in thread
From: Juan Rodrigo Alejandro Burgos Mella @ 2025-12-09 20:40 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Hi
Did you try setting a default value to the field?
Atte
JRBM
El lun, 8 dic 2025 a las 21:40, Igor Korot (<[email protected]>) escribió:
> 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..
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How do I check for NULL
@ 2025-12-09 21:56 David G. Johnston <[email protected]>
parent: Juan Rodrigo Alejandro Burgos Mella <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2025-12-09 21:56 UTC (permalink / raw)
To: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; +Cc: Igor Korot <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
On Tue, Dec 9, 2025 at 1:40 PM Juan Rodrigo Alejandro Burgos Mella <
[email protected]> wrote:
>
> 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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How do I check for NULL
@ 2025-12-09 22:33 Thiemo Kellner <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Thiemo Kellner @ 2025-12-09 22:33 UTC (permalink / raw)
To: pgsql-general <[email protected]>
On 12/9/25 18:29, David G. Johnston wrote:
> On Tue, Dec 9, 2025 at 10:14 AM Thiemo Kellner
> <[email protected]> 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=# 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)
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 <[email protected]>)
id 1vVAKl-00EYBp-0N
for [email protected];
Mon, 15 Dec 2025 15:18:00 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
by malur.postgresql.org with esmtp (Exim 4.96)
(envelope-from <[email protected]>)
id 1vVAKj-000vEw-2Q
for [email protected];
Mon, 15 Dec 2025 15:17:58 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
(Exim 4.96)
(envelope-from <[email protected]>)
id 1vVAKj-000vEm-1F
for [email protected];
Mon, 15 Dec 2025 15:17:58 +0000
Received: from mail-wm1-x344.google.com ([2a00:1450:4864:20::344])
by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
(Exim 4.96)
(envelope-from <[email protected]>)
id 1vVAKi-000nyt-04
for [email protected];
Mon, 15 Dec 2025 15:17:57 +0000
Received: by mail-wm1-x344.google.com with SMTP id 5b1f17b1804b1-4779a637712so24236895e9.1
for <[email protected]>; Mon, 15 Dec 2025 07:17:55 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=cybertec.at; s=google; t=1765811874; x=1766416674; darn=lists.postgresql.org;
h=mime-version:user-agent:content-transfer-encoding:references
:in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date
:message-id:reply-to;
bh=qzsn87/VBzTo1GifiqzVJUtxRGyzbM0AYrJc0ecOCoQ=;
b=NGXBmZPvF9tkJjD2XGPYqzy5RMq4QLNNTlPD4QB3F3dRdE2cjZ/xVCl0C3h8mO7lyt
YJV6cE41MCTLNqTZ+0y1PBLn8uCf1YyU+4e+bht88L7QR0wpDPYztZHaiw5YsDqjWWLP
4Sa4tFx6Jt8wedlkUxM+1Rv4W6fQszuv+BHeJ0dQZRXaFdUPQliMjWX89NWvK7KZH/iI
kdWTm16B2Dg2AEMr5FL2Jr2lG7cCpPmxv/D6S4osy3d8mYbq47akYpaFOh8Qk+6oaOZ/
ZTgRpwIaHjrhlrLkkJk4w8x2GQ/xV77wyzHU6C1uYHmTa3mv3pUC49Atci8h+HOKd4om
PkVA==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=1e100.net; s=20230601; t=1765811874; x=1766416674;
h=mime-version:user-agent:content-transfer-encoding:references
:in-reply-to:date:to:from:subject:message-id:x-gm-gg
:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to;
bh=qzsn87/VBzTo1GifiqzVJUtxRGyzbM0AYrJc0ecOCoQ=;
b=Bnk2o7rkFUKssWh1fMuJSaHgTQKsi62sRwmmScVZ2ocdoLjeOa9Y2oWemsAPxCicli
cO3J2CvL/JyHwKHwc5prSK+cZTIBnmbdfTJuiF11w0PCqIdlC4tq/q2nQrMZ/WhuF6Ky
zY8p1JmdNy4u5cWyWeTKSXDYzHPBWnxCIlanPMN/pkrdG0jgt/jrSTZHhIMc9esarh7m
CDoHxzJ92VML6p5eiKqn2+peOyyJ2Eyow9iqmdagKTx7eB2Hnk7ekrHZZWHGp3TjVTq6
OL2zXXsfeUOUS62e01Hc/CJCoOkrNpCkOCd5XUSvzEsmxOR5C5OxCjlsRFv2qFhiTkEm
1UOQ==
X-Forwarded-Encrypted: i=1; AJvYcCWZ8ksKldOvsycF3cc3QmaImRZBYQtQC3pkpPs0akv6ET4drEPuhIdLXAbn+TxYXDcmaLOrrFbNNlpVZd0B@lists.postgresql.org
X-Gm-Message-State: AOJu0Ywu4G2h4TYaVTjcjmOw30r2+Trcx9ydpogTxrKaKbxQyOYI7XNB
nhmxbA3SpIJvk0aZROBs135l7H+P7BbDthyrDpdwzCH26ufAiUkqn7BJJzHVp7Ph4AQk82+NXyg
ZyQggg3CsD92V
X-Gm-Gg: AY/fxX5rGsIERUkyDYPzszfA6DtG/wkM0MOusifJC82K2qFcGozyA2lTnOML29KaDAa
g86oe54kUq+jhMuiFJmEpo4DUJcWX56d54JpCsZ88cNUxxkjTo/sn+yN5iiPCLkCkyKpL4ap1rN
3GHf7Bsrk3g4+xaEKxdP9s9781MU4J1yCZnsY4ZTDSM9gGQcZyjmSnWeW53r16PAh0hqtQ++4UC
NImnEPbim9sVJnV4P0qTSrT20jvHi5sKWW/JfM8EOVoOMR6d8yZ5NF8kj5+IcEb3UEkfkATmJn4
4Bqb71c8hr3rPdG2ZSxkjijRTvqzdjb6/Y/5d2EVdQks9E4l/9JZNS1ea5Ky4q4qoK8L0vdtJSN
incbQ1G1ZpN6cRlA+FpdlDJNrMkTVJVDH8IT6D/hyQsFYT4eYQcbD1KGMBo2TzVguSdxpqe7UgW
hnwdSnyC4wbgqM9mscWTeEthXEWTNbV4zFOX4nrXee0g==
X-Google-Smtp-Source: AGHT+IFKvTUvIv/AFrqQwYh2yFpbvwp3Gqz0o/YHjEzg1NaRXrig8fJCKfYZ6hWTF6GqyV4840ux5w==
X-Received: by 2002:a05:600c:4e47:b0:477:3e0b:c0e3 with SMTP id 5b1f17b1804b1-47a8f9155c6mr92066245e9.32.1765811873569;
Mon, 15 Dec 2025 07:17:53 -0800 (PST)
Received: from laurenz.albe-K4N0CV00F97414D ([2001:871:70:4d5d:eedd:3dd9:428c:9522])
by smtp.gmail.com with ESMTPSA id 5b1f17b1804b1-47a8f49da20sm190280675e9.5.2025.12.15.07.17.53
(version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256);
Mon, 15 Dec 2025 07:17:53 -0800 (PST)
Message-ID: <[email protected]>
Subject: Re: Operational issues when max_replication_slots is exhausted
From: Laurenz Albe <[email protected]>
To: Ahmed Et-tanany <[email protected]>,
[email protected]
Date: Mon, 15 Dec 2025 16:17:52 +0100
In-Reply-To: <CAD7nQBASDnsxiThkH3=SZLb-=uBT7GJvZBxqY9PYUNHJdSeESA@mail.gmail.com>
References:
<CAD7nQBASDnsxiThkH3=SZLb-=uBT7GJvZBxqY9PYUNHJdSeESA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42)
MIME-Version: 1.0
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/;
List-Subscribe: <https://lists.postgresql.org/manage/;
List-Post: <mailto:[email protected]>
List-Owner: <mailto:[email protected]>
List-Archive: <https://www.postgresql.org/list/pgsql-general;
Archived-At: <https://www.postgresql.org/message-id/bfd667fac146fd2ff451b6c0b0750d7f979cf6b0.camel%40cybertec.at;
Precedence: bulk
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: How do I check for NULL
@ 2025-12-09 22:38 Justin Swanhart <[email protected]>
parent: Thiemo Kellner <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Justin Swanhart @ 2025-12-09 22:38 UTC (permalink / raw)
To: Thiemo Kellner <[email protected]>; +Cc: pgsql-general <[email protected]>
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
On Tue, Dec 9, 2025, 5:33 PM Thiemo Kellner <[email protected]>
wrote:
>
> On 12/9/25 18:29, David G. Johnston wrote:
> > On Tue, Dec 9, 2025 at 10:14 AM Thiemo Kellner
> > <[email protected]> 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=# 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)
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2025-12-09 22:38 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-09 02:39 How do I check for NULL Igor Korot <[email protected]>
2025-12-09 02:44 ` David G. Johnston <[email protected]>
2025-12-09 07:53 ` Igor Korot <[email protected]>
2025-12-09 13:43 ` David G. Johnston <[email protected]>
2025-12-09 16:09 ` Adrian Klaver <[email protected]>
2025-12-09 02:51 ` Ron Johnson <[email protected]>
2025-12-09 02:58 ` Ron Johnson <[email protected]>
2025-12-09 17:14 ` Thiemo Kellner <[email protected]>
2025-12-09 17:29 ` David G. Johnston <[email protected]>
2025-12-09 22:33 ` Thiemo Kellner <[email protected]>
2025-12-09 22:38 ` Justin Swanhart <[email protected]>
2025-12-09 17:16 ` Thiemo Kellner <[email protected]>
2025-12-09 20:40 ` Juan Rodrigo Alejandro Burgos Mella <[email protected]>
2025-12-09 21:56 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox