Re: 42P16 error when dropping and adding column - Mailing list pgsql-bugs

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