Re: fix NOT VALID NOT NULL with ALTER COLUMN SET IDENTITY

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/