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

From David G. Johnston
Subject Re: BUG #17720: pg_dump creates a dump with primary key that cannot be restored, when specifying 'using index ...'
Date
Msg-id CAKFQuwam=1NoSNH=EJRUnuuD8JE_jM8tq22-SYqO1nxzoXFwzg@mail.gmail.com
Whole thread Raw
In response to BUG #17720: pg_dump creates a dump with primary key that cannot be restored, when specifying 'using index ...'  (PG Bug reporting form <noreply@postgresql.org>)
Responses Re: BUG #17720: pg_dump creates a dump with primary key that cannot be restored, when specifying 'using index ...'
List pgsql-bugs
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.
 

pgsql-bugs by date:

Previous
From: Pavel Borisov
Date:
Subject: Re: Sort bug
Next
From: Mats Kindahl
Date:
Subject: Crash during backend start when low on memory