hi.
The attached patch makes the last two statements below fail.
CREATE TABLE notnull_tbl1 (a int, b int);
INSERT INTO notnull_tbl1 VALUES (NULL, 1), (300, 3);
ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID; -- ok
ALTER TABLE notnull_tbl1 ALTER COLUMN a ADD GENERATED ALWAYS AS
IDENTITY; --error
ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID,
ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; --error
but in another case,
CREATE TABLE notnull_tbl1 (a int, b int);
INSERT INTO notnull_tbl1 VALUES (NULL, 1), (300, 3);
ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID;
I am not so sure the below two statements should error out or not?
ALTER TABLE notnull_tbl1 ADD COLUMN c int GENERATED BY DEFAULT AS
IDENTITY, ADD CONSTRAINT nn NOT NULL c NOT VALID;
ALTER TABLE notnull_tbl1 ADD COLUMN d serial, ADD CONSTRAINT nn NOT
NULL c NOT VALID;
From 32dc89c57e47a89d8a0719a690f70a089d738925 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Wed, 3 Sep 2025 16:27:24 +0800
Subject: [PATCH v1 1/1] fix NOT VALID NOT NULL CONSTRAINT with identity column
fix the below problem:
CREATE TABLE notnull_tbl1 (a int, b int);
INSERT INTO notnull_tbl1 VALUES (NULL, 1), (300, 3);
ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID; -- ok
ALTER TABLE notnull_tbl1 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; --expect error
ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID,
ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; --expect error
---
src/backend/commands/tablecmds.c | 20 ++++++++++++++++++++
src/test/regress/expected/constraints.out | 11 +++++++++++
src/test/regress/sql/constraints.sql | 6 ++++++
3 files changed, 37 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 082a3575d62..b9f1ae6788f 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8277,6 +8277,26 @@ ATExecAddIdentity(Relation rel, const char *colName,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("column \"%s\" of relation \"%s\" must be declared NOT NULL before identity can be added",
colName, RelationGetRelationName(rel))));
+ else
+ {
+ HeapTuple contup;
+ Form_pg_constraint conForm;
+
+ contup = findNotNullConstraintAttnum(RelationGetRelid(rel),
+ attnum);
+ if (!HeapTupleIsValid(contup))
+ elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"",
+ colName, RelationGetRelationName(rel));
+
+ conForm = (Form_pg_constraint) GETSTRUCT(contup);
+ if (!conForm->convalidated)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("incompatible NOT VALID constraint \"%s\" on relation \"%s\"",
+ NameStr(conForm->conname), RelationGetRelationName(rel)),
+ errhint("You might need to validate it using %s.",
+ "ALTER TABLE ... VALIDATE CONSTRAINT"));
+ }
if (attTup->attidentity)
ereport(ERROR,
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 3590d3274f0..af448eb9b3b 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -1404,6 +1404,17 @@ ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
ERROR: cannot create primary key on column "a"
DETAIL: The constraint "nn" on column "a" of table "notnull_tbl1", marked NOT VALID, is incompatible with a primary key.
HINT: You might need to validate it using ALTER TABLE ... VALIDATE CONSTRAINT.
+-- cannot set column set identity with an invalid not-null
+ALTER TABLE notnull_tbl1 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
+ERROR: incompatible NOT VALID constraint "nn" on relation "notnull_tbl1"
+HINT: You might need to validate it using ALTER TABLE ... VALIDATE CONSTRAINT.
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID, ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
+ERROR: incompatible NOT VALID constraint "nn" on relation "notnull_tbl1"
+HINT: You might need to validate it using ALTER TABLE ... VALIDATE CONSTRAINT.
+-- cannot set column as serial with a no inherit not-null
+ALTER TABLE notnull_tbl1 ADD COLUMN d serial, ADD CONSTRAINT nn NOT NULL d NO INHERIT;
+ERROR: cannot change NO INHERIT status of NOT NULL constraint "notnull_tbl1_d_not_null" on relation "notnull_tbl1"
+HINT: You might need to make the existing constraint inheritable using ALTER TABLE ... ALTER CONSTRAINT ... INHERIT.
-- ALTER column SET NOT NULL validates an invalid constraint (but this fails
-- because of rows with null values)
ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 1f6dc8fd69f..d31dae0b68e 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -831,6 +831,12 @@ ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
-- cannot add primary key on a column with an invalid not-null
ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+-- cannot set column set identity with an invalid not-null
+ALTER TABLE notnull_tbl1 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID, ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
+
+-- cannot set column as serial with a no inherit not-null
+ALTER TABLE notnull_tbl1 ADD COLUMN d serial, ADD CONSTRAINT nn NOT NULL d NO INHERIT;
-- ALTER column SET NOT NULL validates an invalid constraint (but this fails
-- because of rows with null values)
--
2.34.1
