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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: zedaardv(at)drizzle(dot)com
Subject: BUG #17720: pg_dump creates a dump with primary key that cannot be restored, when specifying 'using index ...'
Date: 2022-12-14 09:07:45
Message-ID: 17720-dab8ee0fa85d316d@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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:

I have a table
create table hamster(under integer, over text);
I create a unique index on the under column with nulls not distinct
create unique index uq_not_distinct on hamster (under) nulls not distinct;
i now create a primary key using the unique index

alter table hamster add constraint pk_hamster primary key using index
uq_not_distinct;
NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index
"uq_not_distinct" to "pk_hamster"
table looks good
\d hamster
Table "moon.hamster"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
under | integer | | not null |
over | text | | |
Indexes:
"pk_hamster" PRIMARY KEY, btree (under) NULLS NOT DISTINCT

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Etsuro Fujita 2022-12-14 10:02:58 Re: BUG #17713: Assert with postgres_fdw in v12
Previous Message okijhhyu 2022-12-14 07:20:21 Sort bug