Re: BUG #19393: pg_upgrade fails with duplicate key violation when CHECK constraint named *_not_null exists

Hi Laurenz,

Thanks for the patch and LGTM for PostgreSQL 18.

I tried to create following table on PG18

benchmark=# CREATE TABLE two_not_null_constraints (
col integer NOT NULL,
CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT NULL)
);
ERROR: duplicate key value violates unique constraint
"pg_constraint_conrelid_contypid_conname_index"
DETAIL: Key (conrelid, contypid, conname)=(16385, 0,
two_not_null_constraints_col_not_null) already exists.

In PG17 I was able to create the table.

benchmark=# CREATE TABLE two_not_null_constraints (
col integer NOT NULL,
CONSTRAINT two_not_null_constraints_col_not_null CHECK (col IS NOT NULL)
);
CREATE TABLE
benchmark=# SELECT conname, contype FROM pg_constraint
WHERE conrelid = 'two_not_null_constraints'::regclass
ORDER BY conname;
DROP TABLE two_not_null_constraints;
-[ RECORD 1 ]----------------------------------
conname | two_not_null_constraints_col_not_null
contype | c

One question during the tests should we confirm the output of pg_constraint
table ? It would make sense during the tests but the current test is also
good to proceed.

benchmark=# SELECT conname, contype FROM pg_constraint
WHERE conrelid = 'two_not_null_constraints'::regclass
ORDER BY conname;
conname | contype
----------------------------------------+---------
two_not_null_constraints_col_not_null | n
two_not_null_constraints_col_not_null1 | c
(2 rows)

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, 5 Şub 2026 Per, 17:52 tarihinde
şunu yazdı:

> On Thu, 2026-02-05 at 15:58 +0100, I wrote:
> > The bug is actually not in pg_upgrade, but in CREATE TABLE. The
> attached patch
> > fixes the problem for me by avoiding given constraint names when
> generating
> > the names for NOT NULL constraints.
>
> ... and here is v2, including a regression test.
>
> Yours,
> Laurenz Albe
>