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.