Thread

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

Álvaro Herrera <alvherre@kurilemu.de> — 2025-12-11T07:27:42Z

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)