On 2025-Sep-03, jian he wrote: > 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
Yeah, I think an error here is correct. ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID, ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; --error I don't understand the difference between this test case and the previous one ... I mean, by this point, the constraint already has a not-null constraint, so asking to add another one does nothing. > 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; Hmm. Here we add the column as identity or serial, which marks it as not-null, and try to add a not-valid constraint to it on top. This results in a valid constraint, so I think it's okay. (A new column cannot have existing violating data.) -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
