From: Álvaro Herrera <alvherre@kurilemu.de>
To: msdnchina@163.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19351: in pg18.1,when not null exists in the table , and add constraint problem.
Date: Thu, 11 Dec 2025 08:27:42 +0100
Message-ID: <202512110716.jpbcheffhdow@alvherre.pgsql> (raw)
In-Reply-To: <19351-8f1c523ead498545@postgresql.org>
On 2025-Dec-10, PG Bug reporting form wrote:
> postgres=# create table test_null_20251210(c1 int not null,c2 text);
> CREATE TABLE
> postgres=# \d+ test_null_20251210
> 数据表 "public.test_null_20251210"
> 栏位 | 类型 | 校对规则 | 可空的 | 预设 | 存储 | 压缩 | 统计目标 | 描述
> ------+---------+----------+----------+------+----------+------+----------+------
> c1 | integer | | not null | | plain | | |
> c2 | text | | | | extended | | |
> Not-null constraints:
> "test_null_20251210_c1_not_null" NOT NULL "c1"
> 访问方法 heap
>
> postgres=# alter table test_null_20251210 add constraint xyzxyz not null
> c1;
> ALTER TABLE
> <<<--- above alter table statement should report 'not null constraint
> exists',
> <<<---- Maybe this problem is a bug ?
Yeah, this is more-or-less intentional behavior from commit 14e87ffa5c54
-- the constraint you're trying to add is compatible with the one that
already exists, so there's no point in throwing an error. This is
consistent with the long-standing behavior of "ALTER TABLE ... SET NOT
NULL" not throwing an error. However, there actually is one point of
incompatibility: the constraint name doesn't match. I ruled out
throwing an error in this case, but maybe we should.
If you try to add a NOT VALID constraint you also get no error. (This
is because we combine both the existing definition and a proposed NOT
VALID constraint and reach the conclusion that a validated constraint is
the correct end result).
If you try to add an incompatible not-null constraint you do get an
error. I think the only case right now is a NO INHERIT constraint. In
the future we'll get NOT ENFORCED constraint and that should also
receive an error:
=# alter table test_null_20251210 add constraint xyzxyz not null c1 no inherit;
ERROR: cannot change NO INHERIT status of NOT NULL constraint "test_null_20251210_c1_not_null" on relation "test_null_20251210"
HINT: You might need to make the existing constraint inheritable using ALTER TABLE ... ALTER CONSTRAINT ... INHERIT.
I do note that the HINT in this case is wrong, and I'll go fix it.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"The saddest aspect of life right now is that science gathers knowledge faster
than society gathers wisdom." (Isaac Asimov)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: alvherre@kurilemu.de, msdnchina@163.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19351: in pg18.1,when not null exists in the table , and add constraint problem.
In-Reply-To: <202512110716.jpbcheffhdow@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox