fix NOT VALID NOT NULL with ALTER COLUMN SET IDENTITY
jian he <jian.universality@gmail.com>
From: jian he <jian.universality@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2025-09-03T08:30:54Z
Lists: pgsql-hackers
Attachments
- v1-0001-fix-NOT-VALID-NOT-NULL-CONSTRAINT-with-identity-column.patch (text/x-patch) patch v1-0001
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;
Commits
-
Prevent setting a column as identity if its not-null constraint is invalid
- d9ffc27291fd 18.1 landed
- cf8be0225389 19 (unreleased) landed