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

On Wed, 2026-02-04 at 17:33 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 18.1
> 
> While I was working on upgrading a PostgreSQL 13 cluster to PostgreSQL 18.1
> I encountered the following problem. pg_upgrade fails if a table has both a
> NOT NULL column and a CHECK constraint with the naming pattern
> {table}_{column}_not_null.
> 
> For example consider the following table
> 
> -- On PostgreSQL 13
> CREATE TABLE orders (
>     id BIGSERIAL,
>     customer_id INTEGER NOT NULL,
>     CONSTRAINT orders_customer_id_not_null CHECK (customer_id IS NOT NULL)
> );
>
> [...]
> 
> pg_restore: error: could not execute query: ERROR:  duplicate key value
> violates unique constraint "pg_constraint_conrelid_contypid_conname_index"
> DETAIL:  Key (conrelid, contypid, conname)=(16413, 0,
> orders_customer_id_not_null) already exists.
> Command was:
> -- For binary upgrade, must preserve pg_type oid
> SELECT
> pg_catalog.binary_upgrade_set_next_pg_type_oid('16415'::pg_catalog.oid);
> 
> 
> -- For binary upgrade, must preserve pg_type array oid
> SELECT
> pg_catalog.binary_upgrade_set_next_array_pg_type_oid('16414'::pg_catalog.oid);
> 
> 
> -- For binary upgrade, must preserve pg_class oids and relfilenodes
> SELECT
> pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('16413'::pg_catalog.oid);
> SELECT
> pg_catalog.binary_upgrade_set_next_heap_relfilenode('16413'::pg_catalog.oid);
> 
> CREATE TABLE "public"."orders" (
>     "id" bigint NOT NULL,
>     "customer_id" integer NOT NULL,
>     CONSTRAINT "orders_customer_id_not_null" CHECK (("customer_id" IS NOT
> NULL))
> );
> 
> -- For binary upgrade, set heap's relfrozenxid and relminmxid
> UPDATE pg_catalog.pg_class
> SET relfrozenxid = '502', relminmxid = '1'
> WHERE oid = '"public"."orders"'::pg_catalog.regclass;

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.

Yours,
Laurenz Albe