42P16 error when dropping and adding column

On Thu, Sep 17, 2026 at 7:23 PM Ludvig Janiuk <ludvig.janiuk@proton.me> wrote:

Hello, it seems that between pqsl 17 and 18, the behavior of the
following reproducer has changed:

====
CREATE TABLE reproducer(id int NOT NULL); ALTER TABLE reproducer DROP
COLUMN "id", ADD COLUMN "id" int, ADD PRIMARY KEY("id");
====

Expected output (this is the case on psql 16 and 17):

====
CREATE TABLE
ALTER TABLE
====

Actual output (on psql (PostgreSQL) 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1)):

====
CREATE TABLE
ERROR: 42P16: primary key column "id" is not marked NOT NULL
LOCATION: index_check_primary_key, index.c:266
====

Thanks for the report!

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 attached patch fixes this by deferring the check until after column
drops and additions have been executed. This prevents the old column's NOT
NULL constraint from being found and ensures that required NOT NULL
constraints are added to the new columns before creating the primary-key
index.

Regards,

--
Fujii Masao

Hi Fujii-san,

Fujii Masao <masao.fujii@gmail.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