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/