Re: BUG #19351: in pg18.1,when not null exists in the table , and add constraint problem.

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)