Re: 42P16 error when dropping and adding column

Thank you both for responding so quickly and reviewing my report.

I'm not familiar with the Postgres bug lifecycle - is there anything
else I need to do? File a formal bug in some tracker? Or has that
already happened?

Best regards,
Ludvig Janiuk

On 2026-09-17 17:01, Manuel Reyes Bravo wrote:
> Hi Fujii-san,
>
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> This issue seems to happen because ALTER TABLE checks for an existing NOT
>> NULL constraint before dropping the old column. So, finding the NOT NULL
>> constraint on that old column causes it to skip adding one for the new
>> column, even though the old constraint will be removed with the old column.
> The same early check also breaks commands that do not drop the column at
> all, only its not-null constraint, and v1 fixes those too. On 18.6 each
> of these fails with "primary key column "a" is not marked NOT NULL":
>
> CREATE TABLE t (a int NOT NULL);
> ALTER TABLE t ALTER COLUMN a DROP NOT NULL, ADD PRIMARY KEY (a);
>
> CREATE TABLE t (a int CONSTRAINT a_nn NOT NULL);
> ALTER TABLE t DROP CONSTRAINT a_nn, ADD PRIMARY KEY (a);
>
> CREATE TABLE p (a int NOT NULL) PARTITION BY RANGE (a);
> CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (0) TO (10);
> ALTER TABLE p ALTER COLUMN a DROP NOT NULL, ADD PRIMARY KEY (a);
>
> The two DROP NOT NULL forms work on 17.11; the DROP CONSTRAINT form has
> no equivalent there.
>
> To see what else v1 changes, I ran 24 ALTER TABLE ... ADD PRIMARY KEY
> scenarios on REL_18_STABLE at 459ef520d8f and master at c9c660e6ae0,
> with and without v1, and compared the errors and the resulting catalog
> state: the pg_constraint rows (with conislocal, coninhcount,
> convalidated and connoinherit) and attnotnull. scenarios.sql is
> attached.
>
> v1 changes exactly nine of them, all failures that go away:
>
> * dropping and re-adding the column: the reported case, a two-column
> key with one column replaced, a partitioned table, an inheritance
> parent, and the PRIMARY KEY subcommand written before the DROP
> COLUMN;
> * the three commands above;
> * the reported case with a row in the table, which now fails with
> "column "id" of relation "t" contains null values", as on 17.
>
> The other fifteen give identical errors and identical catalog state with
> and without v1. Among them are plain ADD PRIMARY KEY on a partitioned
> table and on an inheritance parent, ONLY on a parent whose child has or
> lacks the not-null, an existing NOT VALID or NO INHERIT not-null, USING
> INDEX, SET NOT NULL in the same command, an identity column, and ALTER
> COLUMN TYPE in the same command. The results are the same on both
> branches.
>
> Building a379061a22a and its parent: before it, all nine behave as they
> do with v1; after it, all nine fail with "is not marked NOT NULL". So
> the commit named in the patch is the one.
>
> v1's test covers the DROP COLUMN form only. The attached top-up adds
> the three commands above to the same place in alter_table.sql. With v1
> make check passes on master and REL_18_STABLE; with the tests but
> without the tablecmds.c change, alter_table fails on all four.
>
> Regards,
> Manu

--
//Ludvig Janiuk