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@postgresql.org> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      17720
> Logged by:          reiner peterke
> Email address:      zedaardv@drizzle.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.