diff options
| author | Álvaro Herrera <alvherre@kurilemu.de> | 2025-11-03 15:58:19 +0100 |
|---|---|---|
| committer | Álvaro Herrera <alvherre@kurilemu.de> | 2025-11-03 15:58:19 +0100 |
| commit | cf8be022538937fe9fe22de776fb8cfe6460a784 (patch) | |
| tree | fc3357c197fd15fe5907ac18c5ea16a3eec1e06e /src/test | |
| parent | f242dbcede9cb1c2f60ca31e6ad1141e0713bc65 (diff) | |
Prevent setting a column as identity if its not-null constraint is invalid
We don't allow null values to appear in identity-generated columns in
other ways, so we shouldn't let unvalidated not-null constraints do it
either. Oversight in commit a379061a22a8.
Author: jian he <jian.universality@gmail.com>
Backpatch-through: 18
Discussion: https://postgr.es/m/CACJufxGQM_+vZoYJMaRoZfNyV=L2jxosjv_0TLAScbuLJXWRfQ@mail.gmail.com
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/constraints.out | 4 | ||||
| -rw-r--r-- | src/test/regress/sql/constraints.sql | 3 |
2 files changed, 7 insertions, 0 deletions
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index dda67798cb3..1bbf59cca02 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -1404,6 +1404,10 @@ 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 as generated-as-identity if it has 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 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 0a6290bc571..733a1dbccfe 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -832,6 +832,9 @@ 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 as generated-as-identity if it has an invalid not-null +ALTER TABLE notnull_tbl1 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; + -- 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; |