Fix ADD PRIMARY KEY after dropping and re-adding a column Previously, dropping a NOT NULL column, adding a new column with the same name, and making it a primary key in a single ALTER TABLE command could fail with "primary key column ... is not marked NOT NULL". This happened because ALTER TABLE checked for an existing NOT NULL constraint before dropping the old column. Finding the constraint caused it to skip adding one for the new column, even though the old constraint would be removed with the old column.
Defer this check until after column drops and additions have been executed, so that it examines the columns that will actually form the primary key. Add any required NOT NULL constraints before creating the primary-key index. Backpatch to v18, where this issue was introduced by commit a379061a22a. Reported-by: Ludvig Janiuk <[email protected]> Author: Fujii Masao <[email protected]> Co-authored-by: Manuel Reyes Bravo <[email protected]> Discussion: https://postgr.es/m/[email protected] Backpatch-through: 18 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/dd97e0c8e3823690653cd555090e01fafbb33a85 Modified Files -------------- src/backend/commands/tablecmds.c | 35 +++++++++++++++++++++---------- src/test/regress/expected/alter_table.out | 25 ++++++++++++++++++++++ src/test/regress/sql/alter_table.sql | 26 +++++++++++++++++++++++ 3 files changed, 75 insertions(+), 11 deletions(-)
