Date: Thu, 24 Sep 2026 14:09:09 +0000
To: Manuel Reyes Bravo <manuelreyesbravo@gmail.com>,
 Fujii Masao <masao.fujii@gmail.com>
From: Ludvig Janiuk <ludvig.janiuk@proton.me>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: 42P16 error when dropping and adding column
Message-ID: <9221c3eb-2b4e-42f7-899c-61b59482a38b@proton.me>
In-Reply-To: 
 <CA+bCEdApL1=WLxpRAOXDqBAF5WUbgvHQDSfP33NFRo+vgfB49g@mail.gmail.com>
References: <0a9b4c44-cdff-4ec8-a8fb-b3a8b59e9ba9@proton.me>
 <CAHGQGwFmL6L2OPvPMCkqwrRX7QftuCiJ7RVEb8awDc26b2aVjQ@mail.gmail.com>
 <CA+bCEdApL1=WLxpRAOXDqBAF5WUbgvHQDSfP33NFRo+vgfB49g@mail.gmail.com>
Feedback-ID: 90025008:user:proton
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/9221c3eb-2b4e-42f7-899c-61b59482a38b%40proton.me>
Precedence: bulk

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

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

Best regards,
Ludvig Janiuk

On 2026-09-17 17:01, Manuel Reyes Bravo wrote:
> Hi Fujii-san,
>
> Fujii Masao <masao.fujii@gmail.com> wrote:
>> This issue seems to happen because ALTER TABLE checks for an existing NO=
T
>> 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 colu=
mn.
> 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

--=20
//Ludvig Janiuk