Re: BUG #17720: pg_dump creates a dump with primary key that cannot be restored, when specifying 'using index ...'

On Wednesday, December 14, 2022, PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17720
> Logged by: reiner peterke
> Email address: zedaardv(at)drizzle(dot)com
> PostgreSQL version: 15.1
> Operating system: openSUSE Leap 15.4
> Description:
>
> Do a pg_dump of the database.
> the dump creates the code for a primary key that cannot be restored
> pg_dump -p 5632 -Of tranquility.sql -d tranquility
> on restore, I get the following error
> psql:tranquility.sql:90: ERROR: syntax error at or near "NULLS"
> LINE 2: ADD CONSTRAINT pk_hamster PRIMARY KEY NULLS NOT DISTINCT...
> in the dump itself the create constraint command is
> ALTER TABLE ONLY moon.hamster
> ADD CONSTRAINT pk_hamster PRIMARY KEY NULLS NOT DISTINCT (under);
> which does not work with the NULLS NOT DISTINCT string
>

There is a decent chance that the fix here is to prohibit doing what you
did here - a PK cannot contain nulls in any of its columns so indeed
choosing an index that specifies how nulls behave is non-sensical. That
said, it also doesn’t hurt so long as the column itself is indeed not
null. But extending the syntax doesn’t seem that appealing.

David J.